Instead of using .formula, write your formula in R1C1 Reference style.

Open excel and write your formula (in A1 reference style)
tools|options|general tab|check R1C1 reference style)
steal the formula from the formula bar
change the setting back

And modify your code so that you use:

cells(r,"I").formulaR1C1 = "=yourstolenformulahere"



raphiel2063 wrote:

Hi

I'm trying to have my worksheet perform autoformulas in certain cells as
people keep adding lines to it, which blanks out the formula in the correct
cell, and then moaning because it doesn't work. Because of this I want to set
up a macro so that if they alter/change any details (even if they add a row
in the middle) the formula will be carried out.

I've got most of the macro nailed down but am having trouble with the SUMIF
in vba. (*** section)

U4:BL4 is a row of headings (many of which are repeated)
A2 is the selection I want look up
U3:BL4 is the row with the values I want to sum up
The 1st cell which will have this formula is in the fifth row.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing Then

Dim r As Long
r = Target.Row

If Cells(r, "B").Value < "" Or _
Cells(r, "C").Value < "" Or _
Cells(r, "E").Value < "" Then
*** cells(r,"I").formula=SUMIF($U$4:$BL$4,$A$2,U5:BL5) ***
End If
End If
End Sub

However, I need the macro to be generic so the row number changes with the
cell which is selected. Something like below

cells(r,"I").formula=SUMIF($U$(r-1):$BL$(r-1),$A$2,Ur:BLr)

Any ideas?

--

Dave Peterson