Sige,

Try this version. I don't use ; as my seperator, and forgot that formulas entered in VBA need to
use US ,-based syntax rather than the localized syntax.

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...
Hi Bernie,

I did what you asked .... but upon entering the formula,
I get "Cannot run Visual Basic Macro because of a syntax error."

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 6/02/2006 by guest
'

'
ActiveCell.FormulaR1C1 = _

"=INDEX(SOURCE,MATCH(R20C1,INDEX(SOURCE,,1),0),MAT CH(R19C,INDEX(SOURCE,1,0),0))"
End Sub