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(S Col &
":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

Vlookup in VBA - Driving me crazy
 

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 ,
"James" 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(S Col &
":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