How do you LOOP UNTIL a formula result equals a certain value?

[RobShorter]

Hi,

I'm using the 'Do' and 'Loop until' vba functions so that I can copy a formula down to a certain cell in the column. The only way of identifying this 'certain cell' is if I were to evaluate a formula based on the sum of a range in the column next to it, sum(RC[1],R4C100)=0, where the range is from a variable cell RC[1] to a fixed cell R4C100.

How do I write this in VBA code?

Thanks,
Rob

 

[Andrew Poulsom]

From which cell do you want to copy the formula?

 

[RobShorter]

C5

 

[Andrew Poulsom]

Are you saying that you want to loop until the value in column D is equal and opposite to the value in CV4?

 

[RobShorter]

Sorry, the last (fixed) cell of the range should read R100C4, cell D100

 

[Andrew Poulsom]

That doesn't make it any clearer unfortunately. What would your SUM formula be now, and why the comma in it?

 

[RobShorter]

Here is the sequence of events required:

1. a formula is written in cell C5
2. This formula is copied down column C until the sum of values from cell DX to D100 = 0, where X is the same row as formula.

e.g.

the formula gets copied to cell C6 if sum(D6:D100)>0
the formula gets copied to cell C7 if sum(D7:D100)>0
the formula gets copied to cell C8 if sum(D7:D100)>0

but

the formula would not get copied to cell C8 if sum(D8:D100)=0

apologies for the previous confusion

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim r As Long
    Dim c As Long
    Dim Total As Long
    With Range("C5")
        r = .Row
        c = .Column
        Do
            r = r + 1
            Total = WorksheetFunction.Sum(Range(Cells(r, c + 1), Cells(100, c + 1)))
            If Total = 0 Then Exit Do
        Loop
        .AutoFill Destination:=.Resize(r - .Row)
    End With
End Sub

 

[RobShorter]

Superb. Thank you very much!

How do I change the thread to [SOLVED]?

 

[Andrew Poulsom]

How do I change the thread to [SOLVED]?

There's no need to do that (and there isn't a formal way).