[dannac]

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 every time 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 ?

[JBeaucaire]

Excel has you covered here.

1) Click E3 and press CTRL-L to activate the LIST/TABLE wizard.
2) Define the table as A2:F6... or whatever the current size of the data table happens to be
3) When you're done, press OK to convert your data into an Excel Table.

You will see a blue box around your data, drop down filters in row2 for filtering your data (very cool), and whenever you click inside the table, you will see an expansion row offered on the next empty row below the table, a blue star will be in the first cell.

If you type a value anywhere in that expansion row, Excel will add that row to the data table permanently including copying down any formulas / formatting from the row above. So you column F formula will copy down for you.

[dannac]

Hi JB and thanks for the tip.

Still looking for VBA though as the user I'm setting this up for knows less then I do about Excel.

I got some help on mrexcel forum but not doing what I expected.

I wanted the formula in the col F to be copied down whenever a new line was added by the form.

The code I have does that... but it copies it all the way down col F.
I could have done that manually for so many rows.

That's why I was looking for something to just go as low as the last row of data.

This is also related to another thread here about optionbutton on a form not working, which has been fixed.

I'll try and post the sheet here... scratch that, I think the file is too big
Because the code copies the formula in col F all the way down (65k)

Here's the userform code

Private Sub Calendar1_Click()

    TextBox1.Value = Calendar1.Value
    
End Sub

Private Sub CommandButton1_Click()

    Dim lr As Long
    
    '   Determine next empty row
    Nextrow = _
    Application.WorksheetFunction.CountA(Range("A:A")) + 1
    
    '   Transfers the name
    Cells(Nextrow, 1) = TextBox1.Text
    Cells(Nextrow, 2) = TextBox2.Text
    Cells(Nextrow, 3) = TextBox3.Text
    Cells(Nextrow, 4) = TextBox4.Text
    
    '   Transfers Amount
    If OptionButton1 Then
    Cells(Nextrow, 4) = TextBox4.Value
    Cells(Nextrow, 5) = ""
    End If
    
    If OptionButton2 Then
    Cells(Nextrow, 5) = TextBox4.Value
    Cells(Nextrow, 4) = ""
    End If
    
    '   Clears form for next entry
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    TextBox1.SetFocus
    
    

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(2).Row
Range("F3:F" & lr).Formula = "=IF(A3="""","""",IF(D3="""",E3+F2,F2-D3))"

Application.ScreenUpdating = True
    
    
End Sub

Private Sub CommandButton2_Click()

    UserForm1.Hide
    
End Sub

Private Sub UserForm_Click()

End Sub

Think it's something to do with the line starting with lr

[JBeaucaire]

Remove all the extra unwanted formulas to get your workbook back down to a postable size.

You could put a STOP point on this line of code:

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

And then use F8 to step through the next line of code to see what value is placed into the LR variable.

The next line of code that uses the LR variable looks correct.

[dannac]

Thanks JB

The file was 3575kb.

I deleted formulas in col F. The code copies the formula to all
65000+ rows.

After deleting the formulas, only 42kb.

[dannac]

JB... I seem to have something that will work for me.

Kept getting error message after 1st calculation, but would work fine afterwards.

So I just used a "on error resume next"... looks like it will work.

Please correct if you like or maybe tell me what I have wrong.

Thanks

[JBeaucaire]

Like so, your formula was working harder than it needed to:

Private Sub CommandButton1_Click()
Dim LR As Long, NextRow As Long, LastRw As Long

'   Determine next empty row
    NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    
'   Transfers the name
    Cells(NextRow, 1) = TextBox1.Text
    Cells(NextRow, 2) = TextBox2.Text
    Cells(NextRow, 3) = ComboBox1.Text
    Cells(NextRow, 4) = TextBox4.Text
    
'   Transfers Amount
    If OptionButton1 Then
        Cells(NextRow, 4) = TextBox4.Value
        Cells(NextRow, 5) = ""
    ElseIf OptionButton2 Then
        Cells(NextRow, 5) = TextBox4.Value
        Cells(NextRow, 4) = ""
    End If

'   Enter formula
    Worksheets("Sheet1").Range("F4:F" & NextRow).Formula = "=F3+E4-D4"

'   Clears form for next entry
    TextBox1.Text = ""
    TextBox2.Text = ""
    ComboBox1.Text = ""
    TextBox4.Text = ""
    TextBox1.SetFocus

End Sub

[dannac]

Mine ____ .Formula = "=IF(A3="""","""",IF(D3="""",E3+F2,F2-D3))"

Yours ____ .Formula = "=F3+E4-D4"

Now why didn't I think of that.

Thanks JB