Index, Match, ActiveCell. Row confusion

[Andywal]

Hi All

I need to return to my current worksheet, Say 'Sheet1' , the value in a matrix on a different worksheet, Say 'Sheet 10' by using, I assume, Index, Match,Match lookup formula.

This is complicated by the fact that the values to use for reference for the Index are on 'Sheet1' - Column= 'G' - Row= Activecell.Row and Column= 'H' - Row= Activecell.Row

The result must be returned to 'Sheet1' Column='J' - Row= Activecell.Row

I am totally confused by all the references and shoehorning them into an Index formula.

Is there a simple way to do this?

Any thoughts would be much appreciated

Andy.

 

[Andywal]

Hi All

I need to return to my current worksheet, Say 'Sheet1' , the value in a matrix on a different worksheet, Say 'Sheet 10' by using, I assume, Index, Match,Match lookup formula.

This is complicated by the fact that the values to use for reference for the Index are on 'Sheet1' - Column= 'G' - Row= Activecell.Row and Column= 'H' - Row= Activecell.Row

The result must be returned to 'Sheet1' Column='J' - Row= Activecell.Row

I am totally confused by all the references and shoehorning them into an Index formula.

Is there a simple way to do this?

Any thoughts would be much appreciated

Andy.

If it would help this is what I have so far:

    MyCell = "J" & ActiveCell.Row
    MyWidth = "G" & ActiveCell.Row
    MyHeight = "H" & ActiveCell.Row
    MyCell = INDEX ( Sheet10!C22:P36 , MATCH ( MyHeight,  Sheet10!C22"C36 , 0 ) , MATCH ( MyWidth , Sheet10!C22:P22 , 0 ) )

Needless to say, It doesn't work.
Oops I just said it.

Andy

 

[Andywal]

Just Changed the last line to:

MyCell = Index(Worksheets("Sheet10").Range("C22:P36"), Match(MyHeight, Worksheets("Sheet10").Range("C22:C36"), 0), _
Match(MyWidth, Worksheets("Sheet10").Range("C22:P22"), 0))

Am I getting closer?
Please someone help me out!!!
Andy

 

[Andywal]

Now it is telling me that 'Match' is a [Sub or Function not defined.]

What's happening?
Anyone???
Andy

 

[Norie]

Andy

Are you actually looking up the column/row of the activecell on the other sheet?

What do you actually have on the other sheet?

PS To use worksheet functions in VBA you need to precede them with Application.WorksheetFunction, or just Application.

 

[Andywal]

Hi Norie

The first sheet is an Order form, the second is a pricing matrix.
Dependent on the values supplied to the Index function in [MyHeight] and [MyWidth] it should return the value in the pricing matrix to Column 'J' and Row, the ActiveCell.Row.

I have changed the code to the following:

    MyCell = "J" & ActiveCell.Row
    MyWidth = "G" & ActiveCell.Row
    MyHeight = "H" & ActiveCell.Row
    Range(MyCell) = WorksheetFunction.Index(Worksheets("Cas 1").Range("C22:P36"), WorksheetFunction.Match(MyHeight, Worksheets("Cas 1").Range("C22:C36"), 0), WorksheetFunction.Match(MyWidth, Worksheets("Cas 1").Range("C22:P22"), 0))

But have found that the [MyHeight] and [MyWidth] contain the cell address and not the value in those addresses.
How do I change them to give the values and not the addresses?

Andy.

 

[Norie]

Andy

I'm confused, do you want to look up values or row/column numbers?

Is this how the INDEX/MATCH would look in cell on, say, row 10?

=INDEX('Cas 1'!C22:P36, MATCH(H10, 'Cas 1'!C22:P22,0), MATCH(G10, 'Cas 1'!C22:P22,0))

 

[Andywal]

Hi Norie
I think our replies crossed in the post and you noticed the same thing as I found out.

It should be like this:

=INDEX('Cas 1'!C22:P36, MATCH(1200, 'Cas 1'!C22:P22,0), MATCH(800, 'Cas 1'!C22:P22,0))

And return the value at that reference to [J15] or Active row on the Order Form .

Andy

 

[Norie]

Andy

Where do the 1200 and 800 come from?

If this is the code you are using it looks like the are row/column numbers.

MyCell = "J" & ActiveCell.Row
    MyWidth = "G" & ActiveCell.Row
    MyHeight = "H" & ActiveCell.Row
    Range(MyCell) = WorksheetFunction.Index(Worksheets("Cas 1").Range("C22:P36"), WorksheetFunction.Match(MyHeight, Worksheets("Cas 1").Range("C22:C36"), 0), WorksheetFunction.Match(MyWidth, Worksheets("Cas 1").Range("C22:P22"), 0))

 

[Andywal]

Thank you for your persistence Norie.

The 1200 and 800 come from the values input by hand into column 'G' and 'H' in the Active Row on the Order form.

Andy