Pretty Basic Excel Macro Help Closed

Question

Hello,

I am writing a macro (which will be used to create a VB6 program) in Excel 2003.  

The spreadsheet Update_Log is a database with 9 user input columns and 7 columns which are calculated by formulas based on user input.  I need to create a macro that finds the last record in column A, moves one more row down. and enters the data.  The first column is a primary key which starts with the value 1 (@ A9) and increases sequentially (2, 3, 4, etc.).  A second sheet, Sheet 2, has text boxes to accept the user input.  

If anyone can provide the code for creating the new row, automatically add the Log Key at the next value, and show how to add the input from a txtbox (called OperatortTXT) in the same row to column B, it would be greatly appreciated.

If any clarification is needed, please reply or email.

Thanks,

Reinman

Ivan-hoe · Accepted Answer

Hello, 
I guess you click on a button to validate the data put in the textboxes. 
Your VBA macro could look like this : 

Private Sub CommandButton1_Click()
    Dim LastLine As Integer, NewLine As Integer
    
    With Sheets("Update_log")
        LastLine = .Cells(Rows.Count, "A").End(xlUp).Row
        NewLine = LastLine + 1
        
        If Not IsNumeric(.Cells(LastLine, "A").Value) Then
            .Cells(NewLine, "A").Value = 1
        Else
            .Cells(NewLine, "A").Value = .Cells(LastLine, "A").Value + 1
        End If
        
        .Cells(NewLine, "B").Value = OperatorTXT.Value: OperatorTXT.Value = Empty
        .Cells(NewLine, "C").Value = OperatorTXT2.Value: OperatorTXT2.Value = Empty
    End With
End Sub
Ivan

Pretty Basic Excel Macro Help

1 response

Similar discussions

Subscribe To Our Newsletter!