Hello all, I'm having a hard time trying to figure this one out. All
this code does is look at column F in a report, take the value from the
cell in the active row, and assign it to "Sku". It then checks to see
if a sheet named divcodes is open, if not, it'll open it. From there
it does a vlookup in divcodes and returns the code corresponding to
"Sku". If there's no code, it returns "??".
It was working just fine a few days ago, and now I get a type mismatch
at the vlookup line. From the searching I did in the forums I'm
suspecting its how I assigned the range to dCodePath, but I'm not sure
how I'd fix that. Sku comes back as a string, and I removed CStr()
thinking that may be it, but no dice. Can anybody point me in the
right direction? I'd highly appreciate it:)
Code:
--------------------
Sub DivisionCode(dCode)
'Selects division code for part numbers from external sheet
Dim Sku, dCodePath, Check As Boolean
Dim wb As Workbook, wSht As Worksheet
Application.DisplayAlerts = False
origWB = ActiveWorkbook.Name
Sku = Cells(ActiveCell.Row, 6).Value
'Checks if DivCodes sheet is open
For Each wb In Workbooks
If wb.Name < ThisWorkbook.Name Then
For Each wSht In wb.Worksheets
If wSht.Name = "All skus" Then
Check = True
End If
Next wSht
End If
Next wb
Set wb = Nothing
Set wSht = Nothing
If Check = True Then
dCodePath = Workbooks("Divcodes.xls").Worksheets(1).Range("A2: B11243")
Else
With Application.Workbooks.Open("C:\Documents and Settings\My Documents\Excel\Divcodes.xls")
.Application.ActiveWindow.Visible = False
End With
dCodePath = Workbooks("Divcodes.xls").Worksheets(1).Range("A2: B11243")
End If
Workbooks(origWB).Activate
dCode = Application.VLookup(CStr(Sku), dCodePath, 2, 0)
'Error handling
If IsError(dCode) Then
Workbooks(origWB).Sheets(1).Activate
dCode = "??"
End If
End Sub
--------------------
--
carg1
------------------------------------------------------------------------
carg1's Profile:
http://www.excelforum.com/member.php...o&userid=15271
View this thread:
http://www.excelforum.com/showthread...hreadid=530644