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...
Any ideas or hints greatly appreciated.
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.