View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.programming
SIGE SIGE is offline
external usenet poster
 
Posts: 206
Default Write formula with code ...

Hi Bernie,

That's my baby!!!
Sorry for all the trouble ...

It should only have been:
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


I've never seen the usefulness of the RC-notation (not that I am an
expert ;o))) ) but here it comes in handy!

Thanks a million Bernie