Vba intersection of heading row and column of target cell to get the heading of column

Question

I am trying to get the column of the active cell and then find its intersection with the named range.

Sheet1.cells(3,5).value = Sheet2.cells(Intersect(Range("rngHeadingRowofSheet"), Target.Column)).Value

Target is the active cell whose column it will take and find the value of intersected cell with heading row named range.

But am getting type mismatch error. Can someone please help?

Julian Kuchlbauer · Accepted Answer · 2016-09-01T10:42:05.590

0

Try this:

Sheet1.Cells(3,5).Value = Intersect(Sheet2.Range("rngHeadingRowofSheet"), Target.EntireColumn).Value

edited Sep 01 '16 at 10:42

answered Sep 01 '16 at 09:54

Julian Kuchlbauer

I tried this.The active cell is in some other sheet and the the value fetched is being stored in other sheet, so that is why am doing sheet2.cells(intersect code).value. – Sunaina Sep 01 '16 at 10:34
It gives invalid procedure call or argument – Sunaina Sep 01 '16 at 10:35
In which Code module and which procedure do you have this code? Please try the now edited code – Julian Kuchlbauer Sep 01 '16 at 10:40
No problem, im glad to be of service! – Julian Kuchlbauer Sep 01 '16 at 10:46

1 Answers1