Write formula with code ...
Sige,
Try the macro below.
HTH,
Bernie
MS Excel MVP
Sub TryNow()
Dim myRow As Long
Dim myCol As Integer
Dim myOff As Integer
myRow = Range("B65536").End(xlUp).Row
myCol = Range("IV19").End(xlToLeft).Column
Range("C21", Cells(myRow, myCol)).FormulaR1C1 = _
"=INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0) " & _
";MATCH(R19C;INDEX(Source;1;0);0))"
myOff = myCol - 2
Range(Cells(21, myCol + 1), Cells(myRow, (myCol - 2) * 2 + 2)).FormulaR1C1 = _
"=RC[-" & myOff & "] *INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0)" & _
";MATCH(R19C[-" & myOff & "];INDEX(Source;1;0);0))"
End Sub
"Sige" wrote in message
oups.com...
If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))
respectively.
To get a result in F21, G21, H21
==
F21= C21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))
G21= D21*
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))
H21= E21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))
F22 =C22
**INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MAT CH(C$19;INDEX(SOURCE;1;0);0))
.... for the nr of items in column B
<==
HTH
|