Need first statement to Loop until "False"

[bruce2444]

I have a worksheet built that obtains info from Sheet1(ACV) and enters it into Sheet2(Payment1), however when I run the process, only Line 10 fills and then it stops. I need to loop the first statement of the code, so that it fills all of column 1 until there is no info to fill then I assume the rest of the code will fill in the worksheet accordingly.

If anyone can help and tell me how I need to change this code, I'd appreciate it.

Here is the code I have now for Sheet2(Payment1), I have also attached the worksheet for reference.

Sub Generate_Payment1()

    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A10:A" & Lrow).Formula = "=ACV!A11"
        
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B10:B" & Lrow).Formula = "=ACV!B11"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C10:C" & Lrow).Formula = "=ACV!C11"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D10:D" & Lrow).Formula = "=ACV!E11"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E10:E" & Lrow).Formula = "=IF(F10>0,B10,0)"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("G10:G" & Lrow).Formula = "=E10*F10"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("H10:H" & Lrow).Formula = "=IF(G10>0,0.05,0)"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("I10:I" & Lrow).Formula = "=IF(G10>0,0.05,0)"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("J10:J" & Lrow).Formula = "=G10+(G10*H10)+(G10*I10)"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("K10:K" & Lrow).Formula = "=IF(G10>D10,D10+(D10*H10)+(D10*I10),0)"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("L10:L" & Lrow).Formula = "=IF(F10>0,(ACV!F11/B10)*E10,0)"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("M10:M" & Lrow).Formula = "=IF(N10=""N"",IF(K10-L10<0,0,K10-L10),IF(J10-L10<0,0,J10-L10))"
    
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("N10:N" & Lrow).Formula = "=IF(K10=0,0,""N"")"
    

End Sub

[StephenR]

Not quite sure what you're doing, but see if this works for you. Place in a normal module (Insert > Module) rather than the sheet module. Why have you got two bits of code?

Sub Generate_Payment1()

Dim Lrow As Long

Lrow = Sheets("ACV").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Payment1")
    .Range("A10:C" & Lrow).Formula = "=ACV!A11"
    .Range("D10:D" & Lrow).Formula = "=ACV!E11"
    .Range("E10:E" & Lrow).Formula = "=IF(F10>0,B10,0)"
    .Range("G10:G" & Lrow).Formula = "=E10*F10"
    .Range("H10:H" & Lrow).Formula = "=IF(G10>0,0.05,0)"
    .Range("I10:I" & Lrow).Formula = "=IF(G10>0,0.05,0)"
    .Range("J10:J" & Lrow).Formula = "=G10+(G10*H10)+(G10*I10)"
    .Range("K10:K" & Lrow).Formula = "=IF(G10>D10,D10+(D10*H10)+(D10*I10),0)"
    .Range("L10:L" & Lrow).Formula = "=IF(F10>0,(ACV!F11/B10)*E10,0)"
    .Range("M10:M" & Lrow).Formula = "=IF(N10=""N"",IF(K10-L10<0,0,K10-L10),IF(J10-L10<0,0,J10-L10))"
    .Range("N10:N" & Lrow).Formula = "=IF(K10=0,0,""N"")"
End With

End Sub

[bruce2444]

First and foremost thank you.

The code revision you provided does exactly what I was trying to accomplish with my minimal knowledge of VBA and my attempt to butcher together a code I had read here. It almost worked, but the way you wrote it is much cleaner and works seamlessly.

[StephenR]

My pleasure.