Pretty Basic Excel Macro Help

[Closed]
Report
-
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
-
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

1 reply

Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month