Continue Formula until certain cell reached

[deduwa]

Hi - I have the following code;


Dim lastRow As Long
lastRow = Cells(Rows.Count, "D").End(xlUp).Row

Range("X2:X" & lastRow).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

Is there a way to amend or add to this code so that the SUM formula starts in row 2 of column X and continues down, but stops as soon as the data in column A reads 'Duplicate'

Thanks

 

[Fluff]

How about

Sub Addsum()
   Dim fnd As Range
   
   Set fnd = Range("A:A").find("duplicate", , , xlWhole, , , False, , False)
   If fnd Is Nothing Then Exit Sub
   Range("X2:X" & fnd.Row).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
End Sub

 

[deduwa]

Thanks Fluff - that works. Out of curiosity where are there so many commas after Find( ?

 

[Fluff]

It saves having to type in the Parameter names

 

[Rick Rothstein]

Thanks Fluff - that works. Out of curiosity where are there so many commas after Find( ?

The Find function has 9 arguments, the last 8 of them are optional. In order to specify some of the later arguments one needs to specify the argument prior to it. If you want to use what ever the default is for an optional argument, you simply do not put anything in the argument position reserved for it, hence, you will see two commas next to each other for that argument position.