Excel VBA - Insert row and copy function

Question

Hi, I would need some help with VBA

I have the following file with the days for the month, 10 lines for each. Every day I add some values, never know how many, but when it exceeds 10, I always add the additional lines manually, which is quite annoying. So I figured I am going to create a macro (button), and by pressing it, I am going to get a new line. Managed to do it with the following macro:

Range("B" & Rows.Count).End(xlUp).Select

ActiveCell.Offset(1,0).Select

ActiveCell.EntireRow.Insert

While Column B is always used, I figured the code should find always the very last filled Cell and insert the row below it. So even if I finish adding data for a certain day and start adding data for the next day, the macro will do it fine.

Now my problem is that VBA inserts the line well, but will not take any formula into the new line. I managed to find a code, which inserted the line, and could also copy the line above, but obviously it copied everything including the values, which I don't need. All I would need is a function which I have in Column I.

Column I cell function: =if(G1=0,"",H1-G1), so basically a simple function resulting in the deduction of Column G from Column H.

So what I would need:

• Insert a row always below the last used row (done already)
• And copy the Formula into it from let's say I3 to I4 (?)

Thanks in advance

Peter

Answer 1

Hi,

Thanks for the code. Sadly, for me it doesn't do anything at all. No error message, it just doesn't move. 

Should I do anything special here what is different from the code I have written down?

Peter

Answer 2

Try this:

Sub test()

Dim lRow As Long

lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Rows(lRow).EntireRow.Insert

Cells(lRow, 9) = "=IF(G" & lRow & "=0," & Chr(34) & Chr(34) & ",H" & lRow & "-G" & lRow & ")"

End Sub