Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Skip a row when inserting form data.

0

Hi, I have am building a simple form that I have teams filling out for peoples schedules. I have a macro that pulls the data from the form and adds it to a basic data dumb on a different sheet. 

I am wondering how to write it so my macro will ignore pulling lines of data that are left empty. Example, someone only works 2 days and leaves 3 of the days on the form blank. I'd want the macro to only pull the two days and not create new entries for the 3 empty days. 

Below is my basic macro.

Sub data_input()

    ws_output = "Data"

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

        Sheets(ws_output).Cells(next_row, 1).Value = Range("Associate_ID").Value

        Sheets(ws_output).Cells(next_row, 2).Value = Range("Div_Number").Value

        Sheets(ws_output).Cells(next_row, 3).Value = Range("Store_Number").Value

        Sheets(ws_output).Cells(next_row, 4).Value = Range("Zone_Number").Value

        Sheets(ws_output).Cells(next_row, 5).Value = Range("Date_1").Value

        Sheets(ws_output).Cells(next_row, 6).Value = Range("Start_Hour_1").Value

        Sheets(ws_output).Cells(next_row, 7).Value = Range("Date_1").Value

        Sheets(ws_output).Cells(next_row, 8).Value = Range("End_Hour_1").Value

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

        Sheets(ws_output).Cells(next_row, 1).Value = Range("Associate_ID").Value

        Sheets(ws_output).Cells(next_row, 2).Value = Range("Div_Number").Value

        Sheets(ws_output).Cells(next_row, 3).Value = Range("Store_Number").Value

        Sheets(ws_output).Cells(next_row, 4).Value = Range("Zone_Number").Value

        Sheets(ws_output).Cells(next_row, 5).Value = Range("Date_2").Value

        Sheets(ws_output).Cells(next_row, 6).Value = Range("Start_Hour_2").Value

        Sheets(ws_output).Cells(next_row, 7).Value = Range("Date_2").Value

        Sheets(ws_output).Cells(next_row, 8).Value = Range("End_Hour_2").Value

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("Associate_ID").Value

        Sheets(ws_output).Cells(next_row, 2).Value = Range("Div_Number").Value

        Sheets(ws_output).Cells(next_row, 3).Value = Range("Store_Number").Value

        Sheets(ws_output).Cells(next_row, 4).Value = Range("Zone_Number").Value

        Sheets(ws_output).Cells(next_row, 5).Value = Range("Date_3").Value

        Sheets(ws_output).Cells(next_row, 6).Value = Range("Start_Hour_3").Value

        Sheets(ws_output).Cells(next_row, 7).Value = Range("Date_3").Value

        Sheets(ws_output).Cells(next_row, 8).Value = Range("End_Hour_3").Value

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("Associate_ID").Value

        Sheets(ws_output).Cells(next_row, 2).Value = Range("Div_Number").Value

        Sheets(ws_output).Cells(next_row, 3).Value = Range("Store_Number").Value

        Sheets(ws_output).Cells(next_row, 4).Value = Range("Zone_Number").Value

        Sheets(ws_output).Cells(next_row, 5).Value = Range("Date_4").Value

        Sheets(ws_output).Cells(next_row, 6).Value = Range("Start_Hour_4").Value

        Sheets(ws_output).Cells(next_row, 7).Value = Range("Date_4").Value

        Sheets(ws_output).Cells(next_row, 8).Value = Range("End_Hour_4").Value

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("Associate_ID").Value

        Sheets(ws_output).Cells(next_row, 2).Value = Range("Div_Number").Value

        Sheets(ws_output).Cells(next_row, 3).Value = Range("Store_Number").Value

        Sheets(ws_output).Cells(next_row, 4).Value = Range("Zone_Number").Value

        Sheets(ws_output).Cells(next_row, 5).Value = Range("Date_5").Value

        Sheets(ws_output).Cells(next_row, 6).Value = Range("Start_Hour_5").Value

        Sheets(ws_output).Cells(next_row, 7).Value = Range("Date_5").Value

        Sheets(ws_output).Cells(next_row, 8).Value = Range("End_Hour_5").Value

Range("Dates_And_Times").ClearContents

MsgBox ("Form Submit")

End Sub

Answer
Discuss

Answers

0

Your code represents a very good effort. It's functional and easy to read. I hope you will be able to say the same of my rendering of it. (BTW, there is a "CODE" button in the controls bar above the entry pane which inserts HTML code tags.)

Option Explicit

Sub SaveDate()
    ' 100

    Dim WsIn    As Worksheet        ' input tab
    Dim i       As Integer          ' loop counter

    Set WsIn = ActiveSheet          ' it's better to give a name
    For i = 1 To 5
        Data_Input i, WsIn          ' pass loop counter and input tab to the procedure
    Next i

    WsIn.Range("Dates_And_Times").ClearContents
    MsgBox ("Form Submit")
End Sub

Private Sub Data_Input(ByVal i As Integer, _
                       WsIn As Worksheet)
    ' 100

    Dim R       As Long             ' target row
    Dim Dat     As Variant          ' Date_n value

    With Worksheets("Data")         ' change name to suit
        Dat = Trim(WsIn.Range("Date_" & i).Value)
        If IsDate(Dat) Then         ' skip if there is no date (blank = no date)
            R = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Cells(R, 1).Value = WsIn.Range("Associate_ID").Value
            .Cells(R, 2).Value = WsIn.Range("Div_Number").Value
            .Cells(R, 3).Value = WsIn.Range("Store_Number").Value
            .Cells(R, 4).Value = WsIn.Range("Zone_Number").Value
            .Cells(R, 5).Value = Dat
            .Cells(R, 6).Value = WsIn.Range("Start_Hour_" & i).Value
            .Cells(R, 7).Value = Dat
            .Cells(R, 8).Value = WsIn.Range("End_Hour_" & i).Value
        End If
    End With
End Sub

My code is built on the realisation that each row of entries requires the same code, just with three  small changes. So, by designing a way to make the changes (you laid the infrastructure for that :-)) I could combine the 5 repeats into a single sub called in a loop.

I designed it that the loop will not be interrupted. It will run 5 times without fail. But if it doesn't find a Date_n it won't take any action. Perhaps, testing for a blank as you have suggested is the better solution because the entry will also be skipped if the user entered an invalid date. You may build a message box into the loop at that point that displays if the field isn't blank but it's probably better to give that information at the point of data entry. Note that the user can skip a row in the entry field, and the next row will be recorded if it has a date.

I thought the separate columns for date and times cumbersome. Look at this (The question marks are used if you enter the formula in the Immediate pane.):-

? CLng(Date)  = 44114
? CDbl(Time)  = 0.368148148148148
? CDbl(Now()) = 44114.368148148148148

A date is an integer, time is a fraction. Both can be added to each other and then displayed in a single cell with a format like "dd/mm/yy hh:mm". You can copy the above number to a cell, format it as suggested and see the date and time I took the measure.

How to convert a date to an integer is shown above. Time is a little bit more complicated, depending upon what is entered. However, since 24 hoiurs = 1, one hour must be 1/24, and a minute is 1/60th of that.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login