Solved: using a worksheet function in VBA

[choubix]

hello!

I am working on an interpolation VBA macro.
I need to use the "match" function in the macro.

right now I have this:


Dim CurrTest As Variant
Dim i As Integer

For i = 1 To Range("Time_Steps_Increments").Count
CurrTest = Range("Time_Steps_Increments").Cells(i)
Cells(33 + i, 11) = "=Match("CurrTest", Test)"

Next i

that returns an "expected: end of statement"

I believe this is due to me using "CurrTest"as a parameter in the function.
CurrTest is defined in VBA code but not in the Spreadsheet...
maybe a problem with the concatenation too.

right now I am printing the result on screen to check whether it is correct.

later I'll have to store the result of Match in "j" (and use it too loop)

so my questions are:
- how do I solve the "expected: end of statement"?
- how can I use the worksheet function correctly in VBA?

Thank you for your help :)

+++
Alex

[Norie]

Alex

Is this what you want?

Cells(33+i, 11) = "=Match(" & CurrTest.Address & ", Test)"

[Bob Phillips]

You also need to set CurrTest as you are using it as a range object



Set CurrTest = Range("Time_Steps_Increments").Cells(i)

[Bob Phillips]

You can also do it without a loop



With Range("Time_Steps_Increments")

Cells(34, 11).Resize(.Count).Formula = "=Match(" & .Cells(1, 1).Address(False, False) & ", Test)"
End With

[choubix]

hello Norie and Xld

thanks for your help.
I haven't yet tried the 2nd solution proposed but the first solution works fine :)

I understand the CurrTest.Address allows me to get the value of CurrTest each time it is looped. But why do I use a SET for please?

thanks again! :)


ps: apparently it returns the whole function when I do this:

Set CurrTest = Range("Time_Steps_Increments").Cells(i + 1)
Match = "=Match(" & CurrTest.Address & ", Test)"
cells(33 + i, 1 ) = Match

is it possible to get the end result instead of the formula??

[Zack Barresse]

You must use the Set because you are reffering to setting an object as a variable. If you are referring a variable to a value you can use the Let command.

[Norie]

choubix

CurrTest.Address doesn not get you the value of CurrTest it gets the Address.

[choubix]

ok: I saw the address actually when printing the result on screen instead of the value. I can see clearly a difference.

The thing is, using the following code:

Set CurrTest = Range("Time_Steps_Increments").Cells(i + 1)
Match = "=Match(" & CurrTest.Address & ", Test)"
cells(33 + i, 1 ) = Match


the formula is printed on screen when I actually need the value of Match.
I tried playing with application.worksheetfunction earlier without success thinking it would be the way to go.

does anybody have an idea on how to get the value instead of the formula please?

sorry for my questions but i am a beginner and i feel lost pretty easily when programming in Vba right now...

really appreciate your help. :)

[Bob Phillips]

Set CurrTest = Range("Time_Steps_Increments").Cells(i + 1)
Cells(33 + i, 1 ).Value = Application.Match(CurrTest, Range("Test"))


If you want an exact match use



Set CurrTest = Range("Time_Steps_Increments").Cells(i + 1)
Cells(33 + i, 1 ).Value = Application.Match(CurrTest, Range("Test"),0)

[choubix]

works well :)
thanks a lot!