VLOOKUP

R

Rob H

Hi I am using the following code

If Len(Trim(.GetText(mainloop, 57, 10))) > 0 Then
Range("a4").Offset(CrashLoopTest + reccounter, 0).Value =
Trim(.GetText(mainloop, 57, 10))
CrashLoopTest = CrashLoopTest + 1
End If

to Check if the length of a string (with its trailing and leading spaces
removed) is larger than 0, get the string if it is and write it to cell a4
incrementing the value of recounter (which is a cell) by the value of 1.
This is part of the Crashloop process.

I have created another field on the sheet and require the following code to
be added to the above but arent sure where to put it or if it will need
changing to allow it to work.

=VLOOKUP(RIGHT(A4,2), Values!$A1:$B104,2,FALSE)

This is what i am manually having to add to each cell when the MainLoopTest
process has finished for each row.

I realise I need to interupt the mainlooptest process and take out the =
from the VLOOKUP statement.

Any Help much appreciated.

btw... I had thought of putting it in as you see below but it just spits
out erorrs saying invalid characters ($) and then requests a list seperator.
Is this because I would use the VLOOKUP function in a different way in the
script rather than on the sheet?

If Len(Trim(.GetText(mainloop, 57, 10))) > 0 Then
Range("a4").Offset(CrashLoopTest + reccounter, 0).Value =
Trim(.GetText(mainloop, 57, 10))
VLOOKUP(RIGHT(A4,2), Values!$A1:$B104,2,FALSE)
CrashLoopTest = CrashLoopTest + 1
End If
 
B

Bob Kilmer

Rob,
SheetN.Range("A6").Formula = "=VLOOKUP(RIGHT(A4,2),
Values!$A1:$B104,2,FALSE)", where SheetN identifies the target worksheet
like Sheet1 or Worksheets("SheetName"), Worksheets(1), etc.
Bob
 
D

Don Guillett

Using the function in vba requires modification
Sub lookupsheet()
'=VLOOKUP(A3,Sheet3!A2:I4,9,FALSE)
MsgBox Application.VLookup([A3], [Sheet3!A2:I4], 9, False)
End Sub

try

If Len(Trim(.GetText(mainloop, 57, 10))) > 0 Then
Range("a4").Offset(CrashLoopTest + reccounter, 0).Value =
Trim(.GetText(mainloop, 57, 10))
application.VLOOKUP(RIGHT([A4],2), [Values!$A1:$B104],2,FALSE)
CrashLoopTest = CrashLoopTest + 1
End If
 
Top