Vlookpup with VBA

[vitalis1023]

I am trying to write a function in VBA that uses Vlookup function for a table in excel. Here is what I have so far but I am getting an overflow error. Note: when I set myLookup = RandomValue (that is pulling a random value generated in another function named "RandomValue".

Public Function RandomDay() As Long
Dim myRng As Object
Dim myLookup As Integer
Set myRng = Worksheets("Sheet7").Range("B2:C366")
myLookup = RandomValue

RandomDay = WorksheetFunction.VLookup(myLookup, myRng, 2, True)

Debug.Print RandomDay
End Function

 

[Rick Rothstein]

I am trying to write a function in VBA that uses Vlookup function for a table in excel. Here is what I have so far but I am getting an overflow error. Note: when I set myLookup = RandomValue (that is pulling a random value generated in another function named "RandomValue".

Public Function RandomDay() As Long
Dim myRng As Object
Dim myLookup As Integer
Set myRng = Worksheets("Sheet7").Range("B2:C366")
myLookup = RandomValue

Debug.Print MyLookup

RandomDay = WorksheetFunction.VLookup(myLookup, myRng, 2, True)

Debug.Print RandomDay
End Function

Try adding the line of code I highlighted in red and make sure the myLookup variable contains a value in a range that you expect it to be in.

 

[vitalis1023]

When I added Debug.Print myLookup the code still did not run. I did however, check the function that produces the value RandomValue and it is producing values in the correct range.

 

[Rick Rothstein]

When I added Debug.Print myLookup the code still did not run. I did however, check the function that produces the value RandomValue and it is producing values in the correct range.

Then on what line of code are you getting the "overflow error" you reported in your first message? If it is this one...

myLookup = RandomValue

then try changing your declaration of myLookup from Integer to Long.

 

[vitalis1023]

Just figured it out: this works

Public Function RandomDay() As Long
Dim myRng As Object
Set myRng = Worksheets("Sheet7").Range("B2:C366")

RandomDay = WorksheetFunction.VLookup(RandomValue(), myRng, 2, True)

Debug.Print RandomDay

End Function

 

[Rick Rothstein]

Just figured it out: this works

Public Function RandomDay() As Long
Dim myRng As Object
Set myRng = Worksheets("Sheet7").Range("B2:C366")

RandomDay = WorksheetFunction.VLookup(RandomValue(), myRng, 2, True)

Debug.Print RandomDay

End Function

More than likely your original problem was what I indicated in Message #4... you had the wrong declaration for the variable (myLookup) given the size of the number the RandomValue function was returning to it. By removing the variable and assigning the function return value directly to the first argument of the VLookup function, you bypassed the problem you created by using the wrong data type for myLookup.

 

[Ruddles]

<deleted>

 

[Rick Rothstein]

<DELETED>

Ruddles appears to be a man of few words.

 

[Ruddles]

I played with VLOOKUP for a while and eventually posted a suggestion, but I took so long to do it that the problem was solved by then.

So I deleted it.