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.

 

[Norie]

Try this.

Dim rngMatrix As Range

    Set rngMatrix = Sheets("Cas 1").Range("C22:P36")

    With ActiveCell
        Range("J" & .Row).Value = Application.Index(rngMatrix, Application.Match(Range("H" & .Row), rngMatrix.Columns(1), 0), Application.Match(Range("I" & .Row), rngMatrix.Rows(1), 0))
    End With

 

[Andywal]

Hi Norie
Got it sorted.
I changed the References For [MyWidth] and [MyHeight] as below to give the values and it now all works perfectly.

    MyCell = "J" & ActiveCell.Row
    MyWidth = Range("G" & ActiveCell.Row)
    MyHeight = Range("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))

Many thanks for your help.

Andy.