UDF for multiple heading indexing

[jlclyde]

I am trying to create a UDF that will index through multiple rows of
headings. The top heading in Row 1 is companies. Company 1 is in
merged cells B1:G1. Co. 2 is in merged cells H1:M1. Under this row
of headings is a sub heading of programs that are offered by each
company. They are in B2:C2, D2:E2, F2:G2 for co. 1. They are in
H2:I2, J2:K2, L2:M2 for co. 2. Under this row of sub headings is
another sub heading of quantities of either 10 or 20 for each
program. These are in cells B3 as 10 C3 as 20 for program 1. D3 as
10 E3 as 20 for progrmam 2.... So on. I have item numbers down the
left hand side starting at A4. What is being indexed is the price of
the product.

Is there a way to create a UDF that will let you filter down to the
correct row and column and index? For instance if I had Item # 3 that
would be row 6. If I had company 1 that woudl be cells B1:G1. I also
needed program 2 which is D2:E2, and a quantity of 20 which is in
column E. How to get it to be the answer of the value of E6? I am
not set on UDF, a form or other option to go about accomplishing this
task woudl be greatly appreciated.

Thanks,
Jay

 

[Bob Phillips]

Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As
Variant)
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range

Set cell1 = Rows(1).Find(Co)
Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog)
Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty)
FindValue = Cells(Application.Match(Item, Columns(1), 0),
cell3.Column).Value

End Function



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jlclyde]

Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As
Variant)
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range

    Set cell1 = Rows(1).Find(Co)
    Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog)
    Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty)
    FindValue = Cells(Application.Match(Item, Columns(1), 0),
cell3.Column).Value

End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

Bob,

 

[jlclyde]

Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As
Variant)
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range

    Set cell1 = Rows(1).Find(Co)
    Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog)
    Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty)
    FindValue = Cells(Application.Match(Item, Columns(1), 0),
cell3.Column).Value

End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

Bob,
This is great. I did nto realize that it coudl be this easy. I was
doing all sorts of weird stuff with Address and I was constanly
resizing an array.
Thanks for the help,
Jay

 

[Bob Phillips]

Be aware that there is no error checking so it does assume well formatted
inputs, but you will probably just get #VALUE if there is anything wrong.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As
Variant)
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range

Set cell1 = Rows(1).Find(Co)
Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog)
Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty)
FindValue = Cells(Application.Match(Item, Columns(1), 0),
cell3.Column).Value

End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)








- Show quoted text -

Bob,
This is great. I did nto realize that it coudl be this easy. I was
doing all sorts of weird stuff with Address and I was constanly
resizing an array.
Thanks for the help,
Jay