syntax for using vlookup in an Excel Macro

Posted by Matt on March 23, 2001 5:44 PM

Hi!

Hopefully someone can help me out on this one.

Here's what the code looks like:

For num_of_rows = 3 To ippv Step 1

If Sheets(ppv).Cells(num_of_rows, 2) &LT;&GT; "Anonymous" Then

Cells(num_of_rows, 14) = Application.WorksheetFunction.VLookup(B & num_of_rows, users!usersrange, 4, False)
else

End If
Next num_of_rows

I tried all different syntax's and I can't get this to work.

Any Suggestions?

Thanks!

Posted by David Hawley on March 23, 2001 8:50 PM

Hi Matt

Try it like this:

Dim num_of_rows As Long
Dim ippv As Long, VFind As String
Application.ScreenUpdating = False

For num_of_rows = 3 To ippv

If Sheets(ppv).Cells(num_of_rows, 2) &LT;&GT; "Anonymous" Then
VFind = Range("B:" & num_of_rows)

Cells(num_of_rows, 14) = _
Application.WorksheetFunction.VLookup _
(VFind, Sheets("users").Range("usersrange"), 4, False)

End If

Next num_of_rows
Application.ScreenUpdating = True

Dave

OzGrid Business Applications

Posted by Dave Hawley on March 23, 2001 8:51 PM

Oops Typo!

Dim num_of_rows As Long
Dim ippv As Long, VFind As String
Application.ScreenUpdating = False

For num_of_rows = 3 To ippv

If Sheets("ppv").Cells(num_of_rows, 2) &LT;&GT; "Anonymous" Then
VFind = Range("B:" & num_of_rows)

Cells(num_of_rows, 14) = _
Application.WorksheetFunction.VLookup _
(VFind, Sheets("users").Range("usersrange"), 4, False)

End If

Next num_of_rows
Application.ScreenUpdating = True

OzGrid Business Applications

Posted by Matt on March 26, 2001 9:44 AM

Any other suggestions?

Dave,

Any other suggestions? I had trouble getting this to work.