UserForm Finding a Row with Specific Data, then Populating Data From That Row

[reberryjr]

I’m searching for some guidance on creating a timesheet. I thought I would find something on the web,but I couldn’t find anything that really fit what I’m looking to do.

Scenario: Tom goes to lunch. He opens theworkbook, and launches a UserForm (UF). Tom completes certain data elements such as Name, Date, Activity (inthis case Lunch), and start time. Tomclicks submit, and the data maps over to the workbook. I have this part. Where I’m struggling is, when Tom returns, Iwant him to open the workbook, launch the UF, and then enter his Name, theDate, and the Activity; then click a button. The button should then search the workbook for the data Tom entered inthe UF, and return the data elements from that row. Then, Tom will enter the time he returnedfrom lunch, click submit, and that time maps over to the same row.

I’d appreciate it if anyone has some qualitylinks to information that will assist me.

 

[reberryjr]

I found something that appears as thought it might work, but I'm getting an Object Required error at the Set strActivity line. I'm not sure why, as it's in my declarations.

Private Sub cmd_Find_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim m As Workbook
Dim mAD As Worksheet
Dim rngFound As Range
Dim strAssoc, strDate, strActivity As String
Set m = ThisWorkbook
Set mAD = m.Sheets("Associate_Details")
Set strAssoc = Me.cobo_Assoc.Value
Set strDate = Me.txt_Date.Value
Set strActivity = Me.cobo_Activity.Value

mADLR = mAD.Range("A" & Rows.Count).End(xlUp).Row
Set rngFound = Columns("A").Find(strAssoc, Cells(Rows.Count, "A"), xlValues, xlWhole)
If Not rngFound Is Nothing Then
    strAssoc = rngFound.Address
    Do
        If LCase(Cells(rngFound.Row, "C").Text) = LCase(strDate) And _
            LCase(Cells(rngFound.Row("F").Text) = LCase(strActivity)) Then
                Me.txt_Start.Value = rngFound.Row("D").Value
        End If
        
        Set rngFound = Columns("A").Find(strAssoc, rngFound, xlValues, xlWhole)
    Loop While rngFound.Address <> strAssoc
End If
            
Set rngFound = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Fluff]

You have declared strActivity as a string, which means you don't use Set.
Set is only used for objects.
Also this line

Dim strAssoc, strDate, strActivity As String

is only declaring strActivity as a string, the other two are variant.

 

[reberryjr]

Thanks @Fluff. Now I'm getting an "Object Variable or With Block Variable Not Set" error at the loop.

Private Sub cmd_Find_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim m As Workbook
Dim mAD As Worksheet
Dim rngFound As Range
Dim strAssoc As String
Dim strDate As String
Dim strActivity As String
Set m = ThisWorkbook
Set mAD = m.Sheets("Associate_Details")
strAssoc = Me.cobo_Assoc.Value
strDate = Me.txt_Date.Value
strActivity = Me.cobo_Activity.Value
mADLR = mAD.Range("A" & Rows.Count).End(xlUp).Row
Set rngFound = Columns("A").Find(strAssoc, Cells(Rows.Count, "A"), xlValues, xlWhole)
If Not rngFound Is Nothing Then
    strAssoc = rngFound.Address
    Do
        If LCase(Cells(rngFound.Row, "C").Text) = LCase(strDate) And _
            LCase(Cells(rngFound.Row, "F").Text) = LCase(strActivity) Then
                Me.txt_Start.Value = LCase(Cells(rngFound.Row, "D").Value)
        End If
        
        Set rngFound = Columns("A").Find(strAssoc, rngFound, xlValues, xlWhole)
    Loop While rngFound.Address <> strAssoc
End If
            
Set rngFound = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Fluff]

Which line gives the error?
Also why are you using a loop?

 

[reberryjr]

It's the Loop line. I'm only using it because it was on the code that I found, that seemed like it would do what I needed.

 

[Fluff]

If you remove the loop does it do what you need?