Type mismatch in vlookup
 

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

Type mismatch in vlookup
 

I'll have to read into why it works, but I plugged in the Set statement
before the range assignments on dCodePath and that it got it moving.
Thanks anyhow!


--
carg1
------------------------------------------------------------------------
carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
View this thread: http://www.excelforum.com/showthread...hreadid=530644

Type mismatch in vlookup
 

carg1,

I think you need to specify the data type of dCodePath and use the set
statement when assigning to it:

dim dCodePath as range
....
Set dCodePath = Workbooks("Divcodes.xls").Worksheets(1).Range("A2: B11243")

hth,

Doug

"carg1" wrote in message
...

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

Type mismatch in vlookup
 

change
dCodePath = Workbooks("Divcodes.xls").Worksheets(1).Range("A2: B11243")


to
set dCodePath = Workbooks("Divcodes.xls").Worksheets(1).Range("A2: B11243")

in both places.

change
Dim Sku, dCodePath, Check As Boolean

to
Dim Sku, dCodePath as Range, Check As Boolean

--
Regards,
Tom Ogilvy



"carg1" wrote:


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

Type mismatch in vlookup
 

You should have:

Set dcodepath=

and better stiil add

Dim dcodepath as Range

HTH

"carg1" wrote:


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

Type mismatch in vlookup
 

Hello and thanks to all. I previously tried declaring dCodePath as a
range but with no luck. It kept going to an error. However, I haven't
done since using set, so I'll be making that change. Thanks again!


--
carg1
------------------------------------------------------------------------
carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
View this thread: http://www.excelforum.com/showthread...hreadid=530644