Copy cell down .... when another is filled

[lacogada]

I have a form that fills col A to E.

Have "if" formula in F.

What is the best way to have the "if" formula copied
down everytime a new entry is added ?

I would like it dynamic ... as not to just copy the "if" formula
down so many rows ... not knowing how far to go.

Is there VBA code for doing this ?

 

[lacogada]

Bump .....

 

[John Davis]

Bump .....

Does this work for you? Make sure to test it first

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long

lr = Cells(Rows.Count, 1).End(3).Row

Range("F2:F" & lr).Formula = "=IF(A1=A2,A3,A1)"
End Sub

 

[lacogada]

Thanks John

I adjusted the if formula a lttle, the formula does the correct calculation
but I get debug error and the "range " line is highlighted

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim lr As Long

lr = Cells(Rows.Count, 1).End(3).Row

Range("F2:F" & lr).Formula = "=IF(A3="","",IF(D3="",E3+F2,F2-D3))"

End Sub

 

[lacogada]

....it also stops working after 2 calculations.

Could you explain what the line beginning with "lr" does

 

[John Davis]

....it also stops working after 2 calculations.

Could you explain what the line beginning with "lr" does

Try changing this Range("F2:F" & lr).

To this Range("F3:F" & lr).

Looks like you want to feed the formula from row 3. I assumed row 2. That may be causing the problem.

lr defines the last row of data. It is pointed to Column A. Everything worked when I tested it. However, I don't know the layout of your spreadsheet.

 

[SuperFerret]

Don't you need to add in extra quotes?:

Range("F3:F" & lr).Formula = "=IF(A3="""","""",IF(D3="""",E3+F2,F2-D3))"

 

[John Davis]

Don't you need to add in extra quotes?:

Range("F3:F" & lr).Formula = "=IF(A3="""","""",IF(D3="""",E3+F2,F2-D3))"

That's right!

 

[Roj47]

Daft one to add in and wondered why this is not appropriate to do:

Enter this formula to Column F and copy way, way down the sheet.

=if((d3+1)*(e3+1)=1, "", f2-d3+e3)

Would this cause huge amounts of data and increase the workbook size?

 

[lacogada]

Thanks for the replys ... the new code works, sort of.

As long as data is already on the sheet (below row 2) it works fine.

But if I remove all data ... like starting with a brand new balance,
it actually will not let me input the balance to start with.

If I type in a new balance it changes the cell to as nothing,
but the "if" formula from the code is in that cell.

Seems the code would need to look only below row 2.

Again thanks ... it's close

@Roj47 .... thats why I was looking for VBA code as I did not want to
just copy cells down ... wanted dynamic, to change with data