Macro Coding in Excel

Question

Hi I found this code on StackOverflow and I was wondering how I could modify it (see below code snipping...)

    Sub TestMacro()

        Dim dblRow As Double, dtDate As Date, strProject As String 
        Dim strFalut As String, strProb As String, strSol As String

        dblRow = InputBox("Input on What Row?")
        dtDate = InputBox("Date", , Date)
        strProject = InputBox("Project #")
        strFalut = InputBox("Fault")
        strProb = InputBox("Problem")
        strSol = InputBox("Solution")

        Range("A" & dblRow).Value = dtDate
        Range("B" & dblRow).Value = strProject
        Range("C" & dblRow).Value = strFalut
        Range("D" & dblRow).Value = strProb
        Range("E" & dblRow).Value = strSol

    End Sub

Its a good start to perform the function that I want it to perform...But I was wondering how I could modify it with a vLookUp to search the for the next empty row and begin entering the data there instead of having to manually define a row, which may lead to operator error as these rows become more populated...

Good opportunity to learn UserForm, 6 input boxes is not nice. If you have a Table of list of Projects, Faults, Problem, Solution, you can use ComboBox. Date picker for the Date. — PatricK, Mar 04 '16 at 04:58

score 2 · Accepted Answer · answered Mar 04 '16 at 04:50

You can get the first blank row like this:

Range("A" & Range("A" & Rows.count).end(xlup).row + 1).value = dtDate

Obviously change both instances of A for the column you are updating.

Alternatively update dblRow = InputBox("Input on What Row?") to this dblRow = range("A" & rows.count).end(xlup).row and leave the rest of the code as is.

Macro Coding in Excel

1 Answers1