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
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
CODE
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
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
HTH
Mike
RE: Help with running vlookup command in vb
RE: Help with running vlookup command in vb
CODE
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.