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