×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with running vlookup command in vb

Help with running vlookup command in vb

Help with running vlookup command in vb

(OP)
im having trouble running this subroutine.  When it is finished it will be a loop that will find the maximum value in one column and then tell me the corresponding values in the next column every ten rows for 45000 rows.  Right now I am just trying to get it to work for the first ten rows.


Sub FindMax()
    Dim myRangea As Range
    Set myRangea = Worksheets("Sheet1").Range("A2:A10")
    myMax = Application.WorksheetFunction.Max(myRangea)
    Dim myRange2 As Range
    Set myRange2 = Worksheets("Sheet1").Range("A2:B10")
    myVar = Application.WorksheetFunction_.VLookup(myMax,  myRange2, 2)
   MsgBox myVar
End Sub


thanks,
michelle

RE: Help with running vlookup command in vb

What is the underscore doing before Lookup?

CODE

myVar = Application.WorksheetFunction_.VLookup(myMax,  myRange2, 2)

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

RE: Help with running vlookup command in vb

Michelle,

You don't say what trouble you are having.  Assuming the underscore johnwm pointed out is a typo, I'm guessing the problem is that VLookup is not returning the expected ("correct") value.  If that's the case and assuming your data is not in sorted order, then you need to tell VLookup to return an exact match:

CODE

myVar = Application.WorksheetFunction.VLookup(myMax,  myRange2, 2, False)
By omitting the last parameter, it defaults to True, which tells VLookup to find a close match.  However, the data must be in sorted order for that to work properly.  Generally, if you use the exact match form, your code should handle instances where no match is found, as VLookup will return #NA.  This shouldn't happen in this case, since you are looking for the MAX value of your range.

HTH
Mike

RE: Help with running vlookup command in vb

(OP)
thank you both for your help....it was the underscore before .vlookup.

RE: Help with running vlookup command in vb

You could use:

CODE

dim CurRow as long
dim Max as double 'Assuming you're looking for a number
dim MatchVal as string '? double? long?

Max = 0
for CurRow = 1 to 10 'or however high you want
  if cells(CurRow,1).value > Max then
    MatchVal = cells(CurRow,2).value
  end if
next
msgbox MatchVal

This would be more flexible if you wanted to tweak what you're looking for. I'm not sure if it would run faster or slower than the worksheet function method, though.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources