I am trying to build a quotation package as follows, and could do with some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then have
a macro go and lookup (PRICES) to find the item, then return the value in the
adjacent cell. This should continue until it reaches the end on the items to
be priced. Note that the item could be in any one of 4 sheets, but will only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks

Set a named range for each index column and then call the following function
with

lookupRange(range("SheetName!RangeName"),valueYouA reLookingFor,OffsetColumn)


Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As
Presets) As Variant

On Error Resume Next

Dim mr As Range

Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
SearchDirection:=xlNext, SearchOrder:=xlByColumns)

lookupRange = mr.Offset(0, col - 1).Value


End Function


"Craig McK" wrote in message
...
I am trying to build a quotation package as follows, and could do with some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then
have
a macro go and lookup (PRICES) to find the item, then return the value in
the
adjacent cell. This should continue until it reaches the end on the items
to
be priced. Note that the item could be in any one of 4 sheets, but will
only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks

Hi
Thanks for that

Sorry but could you go back a few steps and explain where I put these lines,
when I insert the Public Function... into the window it red lines on that
line and also Set mr= ... line

As I said no experience with VBA, so finding it a bit hard to get started


"Goofy" wrote:

Set a named range for each index column and then call the following function
with

lookupRange(range("SheetName!RangeName"),valueYouA reLookingFor,OffsetColumn)


Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As
Presets) As Variant

On Error Resume Next

Dim mr As Range

Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
SearchDirection:=xlNext, SearchOrder:=xlByColumns)

lookupRange = mr.Offset(0, col - 1).Value


End Function


"Craig McK" wrote in message
...
I am trying to build a quotation package as follows, and could do with some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then
have
a macro go and lookup (PRICES) to find the item, then return the value in
the
adjacent cell. This should continue until it reaches the end on the items
to
be priced. Note that the item could be in any one of 4 sheets, but will
only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks

See http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
Hi
Thanks for that

Sorry but could you go back a few steps and explain where I put these
lines,
when I insert the Public Function... into the window it red lines on that
line and also Set mr= ... line

As I said no experience with VBA, so finding it a bit hard to get started


"Goofy" wrote:

Set a named range for each index column and then call the following
function
with


lookupRange(range("SheetName!RangeName"),valueYouA reLookingFor,OffsetColumn)


Public Function lookupRange(ByVal rangeName As Range, key As Variant,
col As
Presets) As Variant

On Error Resume Next

Dim mr As Range

Set mr = rangeName.Find(what:=key, LookIn:=xlValues,
lookat:=xlWhole,
SearchDirection:=xlNext, SearchOrder:=xlByColumns)

lookupRange = mr.Offset(0, col - 1).Value


End Function


"Craig McK" wrote in message
...
I am trying to build a quotation package as follows, and could do with
some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet
contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then
have
a macro go and lookup (PRICES) to find the item, then return the value
in
the
adjacent cell. This should continue until it reaches the end on the
items
to
be priced. Note that the item could be in any one of 4 sheets, but
will
only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks

OK Basic Steps are


Once you have read Bob's article, you will do the following

0.) Create your names ranges by selecting the index column on each page and
choosing Insert | Name from the menu and give each one the same name ( They
will be unique by the sheet they are on.
1.) Create a module in Visual Basic Editor ( Available From The Tools | |
Macro Menu )
2.) Add the function in
3.) Create a Macro which you will call your function from,







"Craig McK" wrote in message
...
Hi
Thanks for that

Sorry but could you go back a few steps and explain where I put these
lines,
when I insert the Public Function... into the window it red lines on that
line and also Set mr= ... line

As I said no experience with VBA, so finding it a bit hard to get started


"Goofy" wrote:

Set a named range for each index column and then call the following
function
with

lookupRange(range("SheetName!RangeName"),valueYouA reLookingFor,OffsetColumn)


Public Function lookupRange(ByVal rangeName As Range, key As Variant, col
As
Presets) As Variant

On Error Resume Next

Dim mr As Range

Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
SearchDirection:=xlNext, SearchOrder:=xlByColumns)

lookupRange = mr.Offset(0, col - 1).Value


End Function


"Craig McK" wrote in message
...
I am trying to build a quotation package as follows, and could do with
some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet
contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then
have
a macro go and lookup (PRICES) to find the item, then return the value
in
the
adjacent cell. This should continue until it reaches the end on the
items
to
be priced. Note that the item could be in any one of 4 sheets, but will
only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks

Hi Craig,

If I understand correctly, you could use a worksheet solution with VLOOKUP.
I have a workbook example, thanks to Peo Sjoblom, that looks up across 8
worksheets using Vlookup. Here's the formula, where MySheets is a named
range of a list of the sheet names to be looked at, and the lookup range is
A2:C200 on each of the eight sheets.

I only partially understand the formula even though Peo was kind enough to
e-mail me a fairly long and detailed explanation.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),3,0)

You may, however, be set on using VBA.

Regards,
Howard

"Craig McK" wrote in message
...
I am trying to build a quotation package as follows, and could do with some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then
have
a macro go and lookup (PRICES) to find the item, then return the value in
the
adjacent cell. This should continue until it reaches the end on the items
to
be priced. Note that the item could be in any one of 4 sheets, but will
only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks