VBA with Vlookup/index match data input to sheet depending on Userform Label contents

[vhookup]

Hi I'm producing a user form for the purposes of data entry, I have two queries which I'm hoping the experts here can guide me with.

The first one I'm hoping is a simple tweak in the code, but i have no experience of using formula in vba and not sure how to start it. Pretty sure its a IF function though.

Currently I have the basic day, date and time (label36) displayed on a user form in different labels, however for the nature of this form I need the time to display a fixed time on the half hour every hour if it is between certain time periods, e.g. if between 06:00:00-06:59:59 then display 06:30:00 and so on for the next 17 hours.

Private Sub Userform_Initialize()    Label35 = Format(Now(), "dd/mm/yyyy")
    Label34 = Format(Now(), "dddd")
    Label36 = Format(Now(), "hh:mm:ss")
End Sub

(note for the keen eyed, I have just formatted the label to cut off the :ss of the time as the user doesn't need to see them but thought getting rid entirely would have an adverse effect, they are there just not visible so that shouldn't impact the code)



User enters their data, hits Submit;

This data then gets sent to Lastrow. And here is where my next question comes in, instead of Lastrow can a "lookup" or "index/indexmatch" type formula be used to read the Time Label on the form, then lookup/match in the destination sheet and place in the appropriate cell? (the reasoning for this is sometimes a count can be missed and if lastrow is used when the next data is input it will be in the wrong place)

Userform Submit button code below;

Private Sub Commandbutton1_Click()    Dim LastRow As Long, ws As Worksheet


    Set ws = Sheet59


    LastRow = ws.Range("E" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row


    ws.Range("E" & LastRow).Value = TextBox193.Text
    ws.Range("G" & LastRow).Value = TextBox194.Text
    ws.Range("I" & LastRow).Value = TextBox195.Text
    ws.Range("K" & LastRow).Value = TextBox196.Text
    ws.Range("M" & LastRow).Value = TextBox197.Text
    ws.Range("O" & LastRow).Value = TextBox198.Text
    ws.Range("Q" & LastRow).Value = TextBox199.Text
    ws.Range("S" & LastRow).Value = TextBox200.Text
    ws.Range("U" & LastRow).Value = TextBox201.Text


End Sub

Below is a screengrab of "Sheet59" where the data ends up (59!! yep I know too many sheets - previous iterations of this spreadsheet had data entry sheets with 1 sheet per week) I'm looking to make this more lightweight and user friendly via the Userform and so far have dispensed with 52 sheets.




Any and all help greatly appreciated. Thank you.

 

[vhookup]

I also forgot to mention that the times repeat for each day in a week and for every week in the year so there are 365 - 06:30:00's, 07:30:00's etc etc. Is there a foolproof method so the function isn't fooled? The date in the label on the userform is the same format as on the sheet and could be one of the parameters used as a unique identifier. - Will i need to change these labels to text boxes for excel to be able to read them?

 

[vhookup]

SOLVED with fantastic help from another forum. But this forum has helped me greatly with problems in the past, share the wealth and all that. The code below solved the data input problem, however the time range query remains at large. I've temporarily fixed it by replacing Label36 with a combobox so the times can be selected from there. But it needs to be more rigid so I am continuing the pursuit of the display set time within a time range.

Private Sub Commandbutton1_Click()


Application.ScreenUpdating = False
'    Dim formDate
'    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim Ldate, ComboTime As String
    Dim I As Integer
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
'    Set formDate = UserForm1.Label35
'Combo box on userform diplaying a set time on the half hour (if within a certain range)
'    Set formTime = UserForm1.ComboBox1


LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row      'LastRow for the loop


'Identifying date and time
Ldate = Trim(UserForm1.Label35.Caption)            'date retrieved from Label35  (w/o spaces)
ComboTime = Trim(UserForm1.ComboBox1.Value)            'time selected in the ComboBox1 (w/o spaces)


For I = 1 To LastRow
    If (ws.Range("C" & I).Text = Ldate) And (ws.Range("D" & I).Text = ComboTime) Then  ' if and only if Date and Time match then Found we are at the right row number I
        ws.Range("E" & I).Value = TextBox193.Text
        ws.Range("G" & I).Value = TextBox194.Text
        ws.Range("I" & I).Value = TextBox195.Text
        ws.Range("K" & I).Value = TextBox196.Text
        ws.Range("M" & I).Value = TextBox197.Text
        ws.Range("O" & I).Value = TextBox198.Text
        ws.Range("Q" & I).Value = TextBox199.Text
        ws.Range("S" & I).Value = TextBox200.Text
        ws.Range("U" & I).Value = TextBox201.Text
    End If
Next I
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")




Me.Hide




Application.ScreenUpdating = True
End Sub