vba FormulaR1C1

**.paul.** · 01-25-2009, 07:54 PM

how can i write a FormulaR1C1 in vba?
i want to set the formula in cell V2 to "=IF(SUM(B2:U2)>0,SUM(B2:U2),"")"

how can i do that in my macro?

question2: how do i filldown column V?

**shg** · 01-25-2009, 08:14 PM

That's not an R1C1 formula. If you want to enter the formula you have,

range("v2").formula = "=IF(SUM(B2:U2)>0, SUM(B2:U2), """")"

**.paul.** · 01-26-2009, 11:57 AM

Originally Posted by shg

That's not an R1C1 formula. If you want to enter the formula you have,

range("v2").formula = "=IF(SUM(B2:U2)>0, SUM(B2:U2), """")"

yeah i know. i wanted to convert it to a R1C1 formula...

**rylo** · 01-25-2009, 08:16 PM

Hi

1)

range("V2").formula = "=IF(SUM(B2:U2)>0,SUM(B2:U2),"""")"

2) If you can find the last relevant row, then put it into a variable. Assuming that column A will give you the last row then

lastrow = cells(rows.count,"A").end(xlup).row
range("V2").autofill destination:=range("V2:V" & lastrow)

rylo

**royUK** · 01-26-2009, 02:00 AM

How do you determine where to fill down to? This could be one way

Option Explicit
Sub addFormula()
    Dim LastRow As Long
    With Sheet1    '<-change the sheet reference
        LastRow = .UsedRange.Rows.Count
        .Range(.Cells(2, 22), .Cells(LastRow, 22)).FormulaR1C1 = _
        "=IF(SUM(RC[-20]:RC[-1])>0,SUM(RC[-20]:RC[-1]),"""")"
    End With
End Sub

**.paul.** · 01-26-2009, 12:00 PM

how do i mark this thread SOLVED?

**shg** · 01-26-2009, 12:12 PM

yeah i know. i wanted to convert it to a R1C1 formula...

See VBA Help for the ConvertFormula method.

how do i mark this thread SOLVED?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes