VBA - Error when using Cstr on Variant

[jaspalsd]

Hi,

I am needing to use a variant to find a range of where my keyword is found on multiple rows so I can copy data to another source but when I run the code I have a run time error 1004. The code worked fine when I had defined integers with set values.

I am not an expert in VBA, merely manipulating code I've found on forums to make it relevant for my purposes.

Sub Macro1()


Worksheets("Receivables").Activate 'worksheet name


Dim FSearchRow As Variant 'first row of keyword search
Dim LSearchRow As Variant 'last row of keyword search


FSearchRow = "=MATCH(A2,A:A, 0)"  'A2 is for test purposes as I will ref another worksheet for unique ids
LSearchRow = "=(MATCH(A2,A:A,0)+(COUNTIF(A:A,A2))-1)"
 
 
Dim rng As Range

Set rng = Range("B" & CStr(FSearchRow) & ":" & "B" & CStr(LSearchRow)) 'range will change on variable first and last rows where this part is getting errors

rng.Copy Range("L1:L30") 'test purposes as I will copy to another worksheet


End Sub

Thanks,

Jas

 

[jaspalsd]

Hi,

I am needing to use a variant to find a range of where my keyword is found on multiple rows so I can copy data to another source but when I run the code I have a run time error 1004. The code worked fine when I had defined integers with set values.

I am not an expert in VBA, merely manipulating code I've found on forums to make it relevant for my purposes.

Sub Macro1()


Worksheets("Receivables").Activate 'worksheet name


Dim FSearchRow As Variant 'first row of keyword search
Dim LSearchRow As Variant 'last row of keyword search


FSearchRow = "=MATCH(A2,A:A, 0)"  'A2 is for test purposes as I will ref another worksheet for unique ids
LSearchRow = "=(MATCH(A2,A:A,0)+(COUNTIF(A:A,A2))-1)"
 
 
Dim rng As Range

Set rng = Range("B" & CStr(FSearchRow) & ":" & "B" & CStr(LSearchRow)) 'range will change on variable first and last rows where this part is getting errors

rng.Copy Range("L1:L30") 'test purposes as I will copy to another worksheet


End Sub

Thanks,

Jas

looking back to VBA I've done previously do I have to apply something similar to dim strformulas(x to y) as a variant?

 

[NdNoviceHlp]

Maybe trial CINT instead of CSTR. HTH. Dave

 

[jaspalsd]

Maybe trial CINT instead of CSTR. HTH. Dave

Hi,

Thanks for your suggestion. I am getting a type mismatch error now.

 

[NdNoviceHlp]

Fsearchrow = Application.WorksheetFunction.Match(Range("A2").Value, Range("A:A"), 0)

Lsearchrow = Application.WorksheetFunction.Match(Range("A2").Value, Range("A:A"), 0) + _
Application.WorksheetFunction.CountIf(Range("A:A"), Range("A2").Value) - 1

Set Rng = Range("B" & Fsearchrow & ":B" & Lsearchrow) 'range will change on variable first and last rows where this part is getting errors
Rng.Copy Range("L1") 'test purposes as I will copy to another worksheet
Application.CutCopyMode = False

HTH. Dave

 

[jaspalsd]

Fsearchrow = Application.WorksheetFunction.Match(Range("A2").Value, Range("A:A"), 0)

Lsearchrow = Application.WorksheetFunction.Match(Range("A2").Value, Range("A:A"), 0) + _
Application.WorksheetFunction.CountIf(Range("A:A"), Range("A2").Value) - 1

Set Rng = Range("B" & Fsearchrow & ":B" & Lsearchrow) 'range will change on variable first and last rows where this part is getting errors
Rng.Copy Range("L1") 'test purposes as I will copy to another worksheet
Application.CutCopyMode = False

HTH. Dave

you sir are a genius

If it isn't any bother may I please ask why Application.WorksheetFunction worked? Apologies as I don't use macros often

 

[NdNoviceHlp]

FSearchRow = "=MATCH(A2,A:A, 0)"

Your code was setting the variable to a string like making it equal to "Hello". You could use that type of code to set the formula for a cell value which would then output a value that U could use. For example....

Sheets("Sheet1").Range("A1").Formula = "=MATCH(A2,A:A, 0)"
FSearchRow = Sheets("Sheet1").Range("A1").Value

The above code would give FSearchRow the desired Match value. You also need to use Application.Worksheetfunction because VBA does not have a MATCH function. HTH. Dave

 

[jaspalsd]

Me again,

Doing something stupid. I want to find for test purposes the sum of a range of data based on the first and last row of data from Row 19. When I run the formula it returns the string =SUM(AmountRange) in cell E4.

Looking back at macros I've done previously I do not know where I am going wrong as those worked when I applied a range and filldown.

My main goal is to apply the =XIRR(AmountRange,DateRange)

Can anyone please advise, thanks.

Sub Calculator()


Worksheets("NewCalculator").Activate
Range("E4").Select

Dim DateRange As Long

Dim AmountRange As Long




DateRange = Range("D19:D" & Rows.Count).End(xlUp).Row

AmountRange = Range("E19:E" & Rows.Count).End(xlUp).Row



Range("E4").Formula = "=Sum(AmountRange)"

End Sub

 

[jaspalsd]

Me again,

Doing something stupid. I want to find for test purposes the sum of a range of data based on the first and last row of data from Row 19. When I run the formula it returns the string =SUM(AmountRange) in cell E4.

Looking back at macros I've done previously I do not know where I am going wrong as those worked when I applied a range and filldown.

My main goal is to apply the =XIRR(AmountRange,DateRange)

Can anyone please advise, thanks.

Sub Calculator()


Worksheets("NewCalculator").Activate
Range("E4").Select

Dim DateRange As Long

Dim AmountRange As Long




DateRange = Range("D19:D" & Rows.Count).End(xlUp).Row

AmountRange = Range("E19:E" & Rows.Count).End(xlUp).Row



Range("E4").Formula = "=Sum(AmountRange)"

End Sub

Figured it out looking at old workings, I wrongly put D19 when I should have focused on Column D alone for the last row count