syntax help

[dpaton05]

I have a spreadsheet that is populated from another sheet. Here is a screen shot of it https://www.screencast.com/t/6YANfjeXWzov. As each line is entered and copied across, the code places the new line under the previous line. What is the vba code to enter a formula in columns H and I for each row that I copy across. The formula for the cell H4, must be G4*0.1 and the formula for I4 must be G4+H4. Obviously the references need to change with the row that the new entry is being pasted into. So the second entry into the worksheet must be on row 5 instead of 4.

Here is my code to copy to the sheet.

Sub cmdCopy2()
Worksheets("home").Unprotect Password:="costings"
Application.ScreenUpdating = False

Dim Lastrow As Long



    Lastrow = Worksheets("All Costings").Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    Worksheets("Home").Range("A5:F5").copy
    'work with cell at intersection of LastRow and column A of All Costings worksheet
    With Worksheets("All Costings").Cells(Lastrow, 1)
        'paste values
        .PasteSpecial Paste:=xlPasteValues
        'format date
        .Columns("A").NumberFormat = "dd/mm/yyyy"
        'left align the date cell in column A
        .HorizontalAlignment = xlLeft
    End With

    
    'assign number of first empty row in column B of Combo to Lastrow
    'Lastrow = Sheets("All costings").Cells(Rows.Count, "A").End(xlUp).Row
    
    'copy value in cell K5 of home worksheet
    Worksheets("Home").Range("K5").copy
    
    'paste value in cell at intersection of Lastrow and column E of Combo
    Worksheets("All costings").Cells(Lastrow, 7).PasteSpecial Paste:=xlPasteValues
    


    Worksheets("Home").Range("L5").copy
    Worksheets("all costings").Cells(Lastrow, 8).PasteSpecial Paste:=xlPasteValues
    Worksheets("Home").Range("M5").copy
    Worksheets("all costings").Cells(Lastrow, 9).PasteSpecial Paste:=xlPasteValues
    
    If Worksheets("home").Range("E5") = "Activities" Then
0
    Else
        'I tried to enter something here but I had no idea
        Worksheets("All Costings").Range("Lastrow, 8").Formula = "G",Lastrow * .1
    End If
    
        
  
    'copy value in cell J5 of Home worksheet
   ' Worksheets("Home").Range("j5").copy
    
    'paste value in cell at intersection of Lastrow and column E of Combo
   ' Worksheets(Combo).Cells(Lastrow, 5).PasteSpecial Paste:=xlPasteValues
    
    'format values in columns G to I of Combo
    Worksheets("all costings").Columns("G:I").NumberFormat = "$#,##0.00"
    
    'format cells to be in ascending date order
    Call SortDates

    'cancel Cut or Copy mode
    Application.CutCopyMode = False

    'turn screen updating on
    Application.ScreenUpdating = True

'Worksheets("home").Protect Password:="costings"

End Sub

 

[pgc01]

Hi

Try this test:

Range("H4:H10").Formula = "=G4*0.1"

 

[dpaton05]

.

 

[dpaton05]

Hi

Try this test:

Range("H4:H10").Formula = "=G4*0.1"

It needs to be a dynamic formula, not hard coded into vba as it will need to change with every new line that is added. For instance, the first row will be in row 4 but every additional line added will be in extra rows.

 

[Michael M]

Did you try the formula provided by @pgc01
It is dynamic !
If it really needs to be for each row individually, try something lie this

Worksheets("All Costings").Range("H" & Lastrow).Formula = "=G" & Lastrow & " * .1"

 

[dpaton05]

If it really needs to be for each row individually, try something lie this

Worksheets("All Costings").Range("H" & Lastrow).Formula = "=G" & Lastrow & " * .1"

Thanks Michael, this works perfectly!!!

 

[dpaton05]

Hang on, I forgot to comment out the code that I had that was entering the formula in one of the cells. I will get back to you after some more testing.

 

[dpaton05]

I got that bit working by

Worksheets("All Costings").Range("I" & Lastrow).Formula = "=G" & Lastrow

Where the formula is =G7 or whatever the last row is

Still having problems with this code

        If Worksheets("home").Range("E5") = "Activities" Then


            'paste total formula
            Worksheets("All Costings").Range("I" & Lastrow).Formula = "=G" & Lastrow
            Worksheets("All Costings").Range("H" & Lastrow).Text = "0"
        Else

            
        End If

I don't know how to get the line of code to remove the formula and make the cell 0 is column H, with the line of code: Worksheets("All Costings").Range("H" & Lastrow).Text = "0"

 

[Michael M]

Try

Worksheets("All Costings").Range("H" & Lastrow).value=0