VBA Vlookup - returns no data whatsoever

[Glaswegian]

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...

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.

 

[DonkeyOte]

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 ?

 

[Glaswegian]

Hi

Yes - all is exactly as it was when I created the lookup.

 

[schielrn]

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.

 

[Jonmo1]

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.

 

[Glaswegian]

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....