CODEWITHSUNDEEP
excelvba
AME
AME

Reputation: 5300

Populate new row with VBA button click

I am trying to create a list that adds a new row of data each time a button is clicked. I have the following code assigned to the button when clicked: 

    PurchaseDate = InputBox("Enter Purchase Date:")
    Sheets("TrackRecord").Select
    i = 0
    Row = i + 1
    Range("A2").Select
    ActiveCell.FormulaR1C1 = Row
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=Dashboard!R26C4*(1/Dashboard!R26C12)"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=Dashboard!R26C2"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = PurchaseDate
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=Dashboard!R26C8 + R2C4"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=Waterfall!R[8]C[5]"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:I2"), Type:=xlFillDefault
    Range("F2:I2").Select
End Sub

This code works fine, but I'd like it populate the next row below instead of overwriting the same row each time the button is clicked. I know that I have to iterate through the "Range("A2").select" section, e.g. "Range("A2").select" --> "Range("B2").select" .. But I don't know how to do this in VBA for Excel. That's why I am asking you folks ; ) .

Thanks,

Upvotes: 0

Views: 6388

Answers (1)

Michael Rodrigues
Michael Rodrigues

Reputation: 5137

If you want to persist the next row even when you close excel and open it again, it's better to find the last row each time.

 Dim Row As Long

    Row = GetNextRow(Sheets("TrackRecord"), "A") 

    PurchaseDate = InputBox("Enter Purchase Date:") 

    Sheets("TrackRecord").Select 

    Range("A" & Row).Select 
    ActiveCell.FormulaR1C1 = Row 
    Range("B" & Row).Select 
    ActiveCell.FormulaR1C1 = "=Dashboard!R26C4*(1/Dashboard!R26C12)" 
    Range("C" & Row).Select 
    ActiveCell.FormulaR1C1 = "=Dashboard!R26C2" 
    Range("D" & Row).Select 
    ActiveCell.FormulaR1C1 = PurchaseDate 
    Range("E" & Row).Select 
    ActiveCell.FormulaR1C1 = "=Dashboard!R26C8 + R2C4" 
    Range("F" & Row).Select 
    ActiveCell.FormulaR1C1 = "=Waterfall!R[8]C[5]" 
    Range("F" & Row).Select 
    Selection.AutoFill Destination:=Range("F" & Row & ":I" & Row), Type:=xlFillDefault 
    Range("F" & Row & ":I" & Row).Select 
End Sub 


Private Function GetNextRow(sheet As Worksheet, column As String) As Long

    'Look for the first empty row in the specified column

    Dim Row As Long

    For Row = 1 To 65535
        If sheet.Range(column & Row).Formula = "" Then
            GetNextRow = Row
            Exit For
        End If
    Next Row

End Function

Upvotes: 1

Related Questions