VBA Vlookup - returns no data whatsoever

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hi

I have created a couple of fairly simple spreadsheets to help a colleague. All was working fine but no longer.

A monthly Excel file is received showing fee amounts against a salespersons name. No other data is included (as usual, our super systems cannot produce this data...). To aid reconcilliation, I used an existing spreadsheet containing the location and region for each salesperson and created the following:

Spreadsheet A contains the list of salespersons name, location and region. This list is named - MainList.
Spreadsheet B contains the fee amounts and salespersons name.

Using only ss A, the user simply clicks a button and enters the required month, e.g Apr 08

The code then opens ss B, finds the last row with data, then performs the lookup to obtain the location, writes the location to col G, performs another lookup to obtain the region, writes this to col H - that's all it does.
This was working fine until last week and it now does not return any values at all. It simply blanks cols G & H. I had set it up to blank a cell if the lookup could not return a value but not all cells.

I've edited the code to remove some sensitive server data...
Code:
Private Sub CommandButton1_Click()
Dim strMonth
Dim myResult
Dim nxtResult
Dim myPath As String
Dim wbkMonthly As Workbook
Dim x As Long
Dim finalRow As Long

myPath = "\\server\folder\"

strMonth = Application.InputBox("Enter a Month & Year (use 3 letter format, e.g. 'Oct 07')", "Choose the file", Type:=2)
    If strMonth = False Then Exit Sub
    
Set wbkMonthly = Workbooks.Open(myPath & "Completion and Doc Fees" & " " & strMonth & ".xls")

finalRow = wbkMonthly.ActiveSheet.Range("E65536").End(xlUp).Row

With wbkMonthly.ActiveSheet.Range("G1")
    .Value = "Location"
    .Font.Bold = True
    .Font.Name = "Arial"
    .Font.Size = 10
End With

With wbkMonthly.ActiveSheet.Range("H1")
    .Value = "Region"
    .Font.Bold = True
    .Font.Name = "Arial"
    .Font.Size = 10
End With

For x = 2 To finalRow
On Error Resume Next
    myResult = Application.WorksheetFunction.VLookup(wbkMonthly.ActiveSheet.Range("E" & x), Range("'Completion and Doc Fees Partner Codes.xls'!MainList"), 2, False)
    If Err <> 0 Then
        wbkMonthly.ActiveSheet.Range("G" & x).Value = ""
            Else
                wbkMonthly.ActiveSheet.Range("G" & x).Value = myResult
    End If
Next x

For x = 2 To finalRow
On Error Resume Next
    nxtResult = Application.WorksheetFunction.VLookup(wbkMonthly.ActiveSheet.Range("E" & x), Range("'Completion and Doc Fees Partner Codes.xls'!MainList"), 3, False)
    If Err <> 0 Then
        wbkMonthly.ActiveSheet.Range("H" & x).Value = ""
            Else
                wbkMonthly.ActiveSheet.Range("H" & x).Value = nxtResult
    End If
Next x

wbkMonthly.ActiveSheet.Columns("F:G").AutoFit
End Sub

Any ideas or hints greatly appreciated.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
Have you checked to ensure no one has renamed the sheets, named ranges or adjusted the columns you're using as basis of the vlookup ?
 
Upvote 0

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
I would recommend putting in a breakpoint and walking through the code step by step using F8 and see what values are being passed into the lookup when it reaches that stage and then you can step through and see what exactly is happening. Hope that helps.
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Well, it works for me...

I would suggest the following troubleshooting steps..

1. Does the formula work if you enter it manually into the sheet.
2. Use the formula with ACTUAL Range Reference instead of the named range
3. Check your data for EXACT matches, look for trailing and/or leading spaces.
 
Upvote 0

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
I think I'll scream......

My colleague had inserted a column to the left of the salesperson names - and then hidden it!!! It contains some reference code for each salesperson - aaaaaaarrrrrggggghhhhhh!!!!

Thanks to all for your help and suggestions - I'm off to play with the traffic for a while....
 
Upvote 0

Forum statistics

Threads
1,191,191
Messages
5,985,216
Members
439,948
Latest member
Xearo96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top