Macro to Insert formula every 2nd and 3rd row

Viewed 18

I have a sheet which I'm trying to use to generate filenames automatically in Google Sheets

https://docs.google.com/spreadsheets/d/1PS3OXt4b1FO8RT6t3pRTjkiTUrt_tD7AAjpM-QEnqzc/edit?usp=sharing

In the example, Rows 2, 5, 8 are fix, (but originally it is rows 2,3,4) I would like to use a macro to add the two rows underneath, like on the example.

I have this code so far, but this only creates one row and doesn't adopt the formula correctly in the C column:

Sub inserttexteveryonerow()
Dim Last As Integer
Dim emptyRow As Integer
Last = Range("A" & Rows.Count).End(xlUp).Row
For emptyRow = Last To 2 Step -1
If Not Cells(emptyRow, 2).Value = "" Then
Rows(emptyRow).Resize(1).Insert
Range(Cells(emptyRow, "A"), Cells(emptyRow, "C")).Value = Array("+", "NO-BAG", "=C2&A3&B3")
End If
Next emptyRow
End Sub

I also need the second row, which it doesn't creates, and Ideally this needs to work in Google Sheets. Any help is much appreciated.

1 Answers

not the macro but it could help in the meantime:

={"arrayformula"; ARRAYFORMULA(IF(B2:B="",,VLOOKUP(ROW(C2:C), 
 IF(B2:B="add", {ROW(C2:C), C2:C}), 2, 1)&
 IF(B2:B="add",,"+"&B2:B)))}

enter image description here

Related