Help with running vlookup command in vb

[dmpritch82]

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

 

[johnwm]

What is the underscore doing before Lookup?

myVar = Application.WorksheetFunction[COLOR=red]_[/color].VLookup(myMax,  myRange2, 2)

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

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[msmith]

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:

myVar = Application.WorksheetFunction.VLookup(myMax,  myRange2, 2, [highlight]False[/highlight])

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

 

[dmpritch82]

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

 

[handleman]

You could use:

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.