Ranges in VBA

[Batra]

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

 

[Bob Phillips]

Batra,

Try this in your function

Function SearchSku(Pno As String, WB As String, Sheet As String, iCol As
Long, GetCol As Integer)
Res = ""
Set r = Workbooks(WB).Sheets(Sheet).Range(Cells(1, iCol), Cells(255,
Rows.Count))
Res = Application.VLookup(Pno, r, GetCol, False)
If IsError(Res) Then
SearchSku = ""
Else
SearchSku = Res
End If

End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[George Nicholson]

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,

 

[Batra]

HI Bob
Thanks for help, unfortunately for some reason its still
not working.
I had to change the a bit in below line ( Last Cells
(255,rows.count) function as It seemd as typo

Set r = Workbooks(wb).Sheets(Sheet).Range(Cells(1, iCol),
Cells(Rows.Count, 255))

It just failes after this i.e it will never find the value
even if it exist so I am assuming this method of creating
range did not work. Howevere using the old A1 format still
works, but I cant get Input box to get value in that format

Please advise

Thanks
Batra

 

[Batra]

Thanks Bob
Its working now, sorry I had some typo's which messed it
up & my ON Error resume next statement kept errors hidden

Thanks again

 

[Batra]

Thanks a lot Geroge
It worked beautifully
Thanks again