Vlookup in VBA - Driving me crazy

Hi Experts

I would highly appreciate if you could help me solve this mystery.

I have created the following function which let me perform Vlookups on any
workbook.sheet.
Its part of a bigger project & I have put it on server for everyone on
access it.
It works fine when I run it on my machine which has XL 2003 sp1 & so dose it
works fine on may other machines, however some of the users (with same EXCEL
version) get the following error

"Subscript out of range" on line with staement <Set wks =
Workbooks(WB).Sheets(Sheet)> on debusing Sheet = "subscript out of range"

The crazy thing is it works without hitch on mine & as well as few other
machines, just few of them it does not, what could it be, the progam
code/files calls all remain same???

Please advise as I am pulling my hair out as this where my VBA knowledge ends.


Function SearchSku(Pno As String, WB As String, Sheet As String, SCol As
Long, GetCol As Long)
Res = ""
'Set r = Workbooks(ThisWorkbook.Name).Sheets(Sheet).Range(SCol &
":IV60000")
Dim wks As Worksheet

Set wks = Workbooks(WB).Sheets(Sheet)
Set r = wks.Range(wks.Cells(1, SCol), wks.Range("IV60000"))

Res = Application.VLookup(Pno, r, GetCol, False)
If IsError(Res) Then
SearchSku = "n/a"
Else
SearchSku = Res
End If

End Function



Thanks a lot

Not much to tell except that the error indicates that there's no sheet
Sheet in Workbook WB.

Have you checked for extraneous characters in the sheet name (or in the
input)?

Are you sure you're calling the right WB? Is it open on others' machines?

In article <[email protected]>,
"James" <[email protected]> wrote:

> Hi Experts
>
> I would highly appreciate if you could help me solve this mystery.
>
> I have created the following function which let me perform Vlookups on any
> workbook.sheet.
> Its part of a bigger project & I have put it on server for everyone on
> access it.
> It works fine when I run it on my machine which has XL 2003 sp1 & so dose it
> works fine on may other machines, however some of the users (with same EXCEL
> version) get the following error
>
> "Subscript out of range" on line with staement <Set wks =
> Workbooks(WB).Sheets(Sheet)> on debusing Sheet = "subscript out of range"
>
> The crazy thing is it works without hitch on mine & as well as few other
> machines, just few of them it does not, what could it be, the progam
> code/files calls all remain same???
>
> Please advise as I am pulling my hair out as this where my VBA knowledge ends.
>
>
> Function SearchSku(Pno As String, WB As String, Sheet As String, SCol As
> Long, GetCol As Long)
> Res = ""
> 'Set r = Workbooks(ThisWorkbook.Name).Sheets(Sheet).Range(SCol &
> ":IV60000")
> Dim wks As Worksheet
>
> Set wks = Workbooks(WB).Sheets(Sheet)
> Set r = wks.Range(wks.Cells(1, SCol), wks.Range("IV60000"))
>
> Res = Application.VLookup(Pno, r, GetCol, False)
> If IsError(Res) Then
> SearchSku = "n/a"
> Else
> SearchSku = Res
> End If
>
> End Function
>
>
>
> Thanks a lot