Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need HELP!! IF OR formula driving me crazy!!!! | Excel Discussion (Misc queries) | |||
Driving me CRAZY~ please help | New Users to Excel | |||
VLOOKUP driving me crazy | Excel Discussion (Misc queries) | |||
It doesn't add up - It's driving me crazy | Excel Programming | |||
Driving me crazy! | Excel Programming |