using vlookup string in vb

[daniroy]

hello there and thanks in advance

I am using a string to setup a variable in VB.
The following code is working fine untill I want to introduce a variant
from i = 10 to 100
______________________________________
'GOOD CODE'

Sub Calculate()

Dim D_Y_String As String
Dim D_Y As Single


D_Y_String = "=VLOOKUP(FT!F10,D4:I10,6)"
D_Y = Application.Evaluate(D_Y_String)

End Sub
_______________________________________

To be fair, I then inject value of D_Y into another function.

The point is: here "=VLOOKUP(FT!F10,D4:I10,6)" is setup for one
specific cell FT!F10

But I will need to go through F10 to F100
There I did try the code

______________________________________

Sub Calculate()

Dim D_Y_String As String
Dim D_Y As Single

For i = 10 To 100

D_Y_String = "=VLOOKUP(Sheets("FT").Range("F"&i).Value,D4:I10,6)"
D_Y = Application.Evaluate(D_Y_String)


End Sub
_____________________________________

But obviously it is not working ... the big issue is that I do not know
how to properly introduce a variant into the vlookup code ...

Any idea and help would be, as usual, deeply appreciated!.

Thanks to everybody

regards
Daniel

 

[Bob Phillips]

Why did you not just stick with the same approach.

Sub Calculate()

Dim D_Y_String As String
Dim D_Y As Single

For i = 10 To 100

D_Y_String = "=VLOOKUP(FT!F"& i & ",D4:I10,6)"
D_Y = Application.Evaluate(D_Y_String)


End Sub
_____________________________________

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[daniroy]

Hello Bob and many thanks for your answer.
I am afraid the problem is a synthax error here:

D_Y_String = "=VLOOKUP(FT!F"& i & ",D4:I10,6)"

FT!F"& i & " does not looks to work as neither FT!"F"&i nor FT!"F"&i

I do not know what should be the correct one, nor what is the default
synthax for that, btw... so a light on that point would be more than
good !

best regards
Daniel

 

[Bob Phillips]

That code works fine for me. It does need a

Next i

after the Evaluate line, but that line does not cause me a syntax error.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[daniroy]

VB is as wonderfull as Excel. It indeed works perfectly, and have the
Next i
But The problem sounds now to be
D_Y = Application.Evaluate(D_Y_String)
As I now reach a Type Mismatch error when on that part of the code!
It breaks my small brain...

thanks Bob!
DR