Think about how you would set this spreadsheet up in the Excel interface First you would enter a formula in Cells D F G Nextyou would copy these cellsthen paste them the rest of the way down the columnThe code might look something like this

Sub A1Style()

1 Locate the FinalRow

FinalRow = Range("B65536").End(xlUp).Row 1 Enter the first formula Range("D4").Formula = "=B2*C2"

Range("F4").Formula = "=IF(E4,ROUND(D4*$B$1,2),0)" Range("G4").Formula = "=F4+D4"

' Copy the formulas from Row 4 down to the other cells Range("D4").Copy Destination:=Range("D5:D" & FinalRow) Range("F4:G4").Copy Destination:=Range("F5:G" & FinalRow) 1 Enter the Total Row Cells(FinalRow + 1, 1).Value = "Total"

Cells(FinalRow + 1, 6).Formula = "=SUM(G4:G" & FinalRow & ")" End Sub

0 0

Post a comment