RB:

In SearchSKU:
1) I believe "SCol As String" should be "SCol As Integer". It gets its value
from MPCol which seems to be a numerical value.)

2) Then try establishing your range using the Range(cell1, cell2) syntax.
In the following example R,C notation is used for Cell1 and A1 notation is
used for Cell2.

Dim wks as Worksheet

Set wks = Workbooks(WB).Sheets(Sheet)
Set r = wks.Range(wks.Cells(1, SCol), wks.Range("IV60000"))
(assuming you want to set the range to start at row 1)


Hope this helps,

--
George Nicholson

Remove 'Junk' from return address.



"Batra" wrote in message
...
Hi Guys

I hope you could help me out. I am building a general
search routine but getting stuck & manuplating range. I
have give the partial example below. It is supose to ask
the user select a column containg the list to search for
then. So its bacically an automated Vlookup routine.
However the part it fails is when passing on the column #
to the function. The passed value is a integer where as
function accepts range in "A1" style. I dont know how to
change etier method to make it work. Please advise . I use
Excel 2002 on Windows 2000

Sub test()
' ask the user which column has the list of part numbers
to search for

Set MPLPrice = Application.InputBox _
(Prompt:="Please Select the Sku Column to
Search", Title:=".", Type:=8)

If MPLPrice.Cells.Count = 0 Then Exit Sub
If MPLPrice.Columns.Count < 1 Then
MsgBox "You Can Only Select One Column for Sku Search"
& vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If
' get the column number
MPLcol = MPLPrice.Column


' example of search, pass the part number, workbook &
worksheet + serach colun & search value to get column
PartNo = "1234-b21"
Res = SearchSku(PartNo, "ABC.XLS", "PriceList", MPLcol, 9)
MsgBox Res

End Sub

Function SearchSku(Pno As String, WB As String, Sheet As
String, SCol As String, GetCol As Integer)
Res = ""
Set r = Workbooks(WB).Sheets(Sheet).Range(SCol
& ":IV60000")
Res = Application.VLookup(Pno, r, GetCol, False)
If IsError(Res) Then
SearchSku = ""
Else
SearchSku = Res
End If

End Function

Thank you so much in advance
RB