PDA

View Full Version : Error when using vlookup in VBA



xluser2007
01-05-2008, 07:01 PM
Hi All,

I wish to do a vlookup on a date in A1 in the Activeworksheet. The vlookup is to be applied in an open workbook called "F01hist.xls", in the worksheet called "F01HIST.xls" on the range A1:B5000.

Here is the code I have used:

Sub vlookup()

On Error Resume Next
found = Application.vlookup(Format$(ActiveSheet.range("A1"), dd / mm / yy), Workbooks("F01hist.xls").Worksheet("F01HIST.XLS").range("A1:B5000"), 2, 0)
If IsError(found) Then
MsgBox Format$(ActiveSheet.range("A1"), dd / mm / yy) & " not found"
Else: MsgBox "The look-up value of " & Format$(ActiveSheet.range("A1"), dd / mm / yy) & " is " & found & " in column " & 2
End If
On Error GoTo 0

End Sub
Although the MsgBox pops up (meaning that it doesn't recognise an error with the 'found' vlookup variable), it does not print the value of 'found' in the Msgbox.

I have manually checked that the A1 value that is being looked up is valid and that a manual excel vlookup returns the value as desired.

Could anyone please clarify what is wrong with the above code?

rlv
01-05-2008, 08:23 PM
I think you may have overlooked ".WorkSheetFunction" in the VLookup syntax. Try "found = Application.WorkSheetFunction.VLookup(.... " One other problem I notice is that you will need to put quotes around your format spec "dd/mm/yy". Lastly, I see that you use "0" for the Range_Lookup parameter. I normally use "false" myself, but now you have me curious to see if "0" will work. Good luck.

xluser2007
01-05-2008, 08:57 PM
Hi rlv, thanks for your reply.

Just tried a range of thos approaches, nbot yet successful.

Went back to basics and had my target workbook "F01hist.xls" open.

Then tested whether the following code was working correctly:

Sub range_select()

With Workbooks("F01hist.xls").Worksheets("F01HIST.XLS")
.Range("A:B").Select
End With

End Sub
This worked, so I decided to simplify my orginal code as follows:

Sub vlookup()

Dim shtUse As Worksheet

Set shtUse = ActiveSheet

With Workbooks("F01hist.xls").Worksheets("F01HIST.XLS")
y = Application.vlookup(Format$(31 / 10 / 2007, "dd/mm/yyyy"), .Range("A:B"), 2, False)
End With

MsgBox y

End Sub
The program failed at MsgBox y step, clearly it is not picking up the vlookup value.

Any ideas where I;m worng in the Sub vlookup() code above?

rlv
01-05-2008, 10:45 PM
With VLookup, the search parameter is sensitive to data type. It may be it is choking on the format string you are feeding it. I have always had trouble using string types for that. By using the string function FORMAT$ instead of the variant function FORMAT you may have inadvertently invited that kind of trouble.

To be safe, make sure you are using a variant data type.
The lookup table should be a range.

I were going to rewrite what you have, it woud look like this:



Sub AltVLookup()

Dim MyResult As Variant
Dim SearchTerm As Variant

Dim DataWB As Workbook
Dim DataWS As Worksheet

Set DataWB = Workbooks("F01hist.xls")
Set DataWS = DataWB.Worksheet("F01HIST.XLS")
Set DataRange = DataWS.Range("A1:B5000")

SearchTerm = Format(ActiveSheet.Range("A1"), "dd/mm/yy")

On Error Resume Next
MyResult = Application.WorksheetFunction.vlookup(SearchTerm, DataRange, 2, False)

If VBA.IsEmpty(MyResult) Then
MsgBox SearchTerm & " not found"
Else
MsgBox "The look-up value of " & SearchTerm & " is " & MyResult & " in column 2"
End If

On Error GoTo 0

End Sub

Bob Phillips
01-06-2008, 03:11 AM
You do not need Worksheetfuunction as well as application, the two work in different ways, and you use the appropriate one.

When looking at a worksheet it is part of the WorksheetS collection, not Worksheet.

The date is not formatted to lookup with/

And surely, the worksheet in the other workbook is NOT the same as the workbook name.



Dim found As Variant

On Error Resume Next
found = Application.vlookup(CLng(ActiveSheet.Range("A1").Value), _
Workbooks("F01hist.xls").Worksheets("that_sheet_name").Range("A1:B5000"), 2, False)
On Error GoTo 0
If IsError(found) Then
MsgBox Format$(ActiveSheet.Range("A1"), "dd/mm/yy") & " not found"
Else
MsgBox "The look-up value of " & _
Format$(ActiveSheet.Range("A1").Value, "dd/mm/yy") & " is " & found & " in column " & 2
End If

xluser2007
01-06-2008, 04:24 AM
Thnka rlv and xld,

xld, your amendments to rlvs code amendments work a treat!

Also, the "F01hist.xls" workbook has a sheet called "F01HIST.xls", it is a downloaded spreadsheet which I don't make but have to use. Not the best design for transparent coding, I agree.

Also as a small query for the correct worksheet to use I wanted to define it as follows:



Dim ParameterSht As Worksheets

Set ParameterSht = Workbooks("Update_Interest_Macro").Worksheets("parameters")


This gives a subscript out of range error when defining ParameterSht, any idea what to change?

All of your help is very appreciated :hi:. Thank you

Bob Phillips
01-06-2008, 05:04 AM
My code is in response to yours, I didn't even look at rlv's code when I wrote my response, apart from noticing his erroneous comments about adding Worksheetfunction..

Worksheets is a collection, the variable is a single object, so it should be dimensioned as Worksheet, singular.

xluser2007
01-06-2008, 03:28 PM
Cheers xld.

[Sorry I thought you had amended rlvs code, didn't realise you hadn't.]

xluser2007
01-06-2008, 07:36 PM
Now that the vlookup is getting the value from the relvant spreadsheet, i wanted to try to get the cell reference for the value.

That way for example the vlookup will get the relevant value for the 'found variable' from 'F01hist.xls' which is in cell "B472".

In the macro spreadhsheet cell A5, I therefore wanted to also output the filepath and cell reference of the found variable in say Cell A5 of the macro spreadsheet

i.e.A5 should read the string C:\Documents\[F01hist.xls]F01HIST.XLS'!$B$472, the full source of the filepath for the found value.

Anyone have any ideas how to extract this fiepath for the vba variable given xld's vlookup code for the relevant value?