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

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

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,

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" <carg1.25uvkn_1144348804.899@excelforum-nospam.com> wrote in message
news:carg1.25uvkn_1144348804.899@excelforum-nospam.com...
>
> 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
>

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

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

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!