Find and update

[Naitnoum]

Good day,

I need a little help with a project I'm completing. What I'm trying to do is have a button find a value in column 'B' on the second sheet of the workbook. The value the button is looking for will be user generated in cell C4 of sheet 1. Once the value has been found I want the button to update the cell in the fifth column in the same row with todays date. Right now my issue is with locating the cell I want to be updated. I've searched through multiple message boards, including this one, and have yet to find anything I could use let alone understand.

So far my code looks like so:

Private Sub CommandButton2_Click()
Dim TicketNum As String
Dim strCheck As String
TicketNum = Workbooks("PensionDataCorrection.xls").Sheets(1).Range("C4")
With Workbooks("PensionDataCorrection.xls").Sheets(2).Range("b:b")
    On Error Resume Next
    strCheck = .Find(what:=TicketNum, After:=Cells(1, 1), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=ByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)
    MsgBox "Value is in column: " & strCheck
    On Error GoTo 0
    End With
If strCheck <> vbNullString Then
    MsgBox "Does Not Exist"
    End If
End Sub

The find doesn't work so well. I'm obviously doing something wrong. I designated the value from cell C4 as a string variable as it can, and will, change constantly. I can have todays date entered in a cell but locating the cell in the fifth column for the row where the "TicketNum" variable resides is my big issue. I'm unfamiliar with much of vba sintax. I know I can do a =Match in excel to find the row but how do I have vba do the same and then update the fifth column in that row?

Thank you in advance to anyone who responds.

 

[Noz2k]

I think Strcheck needs to be a range and then you just need to add an offset to refer to the 5th column.

so

Private Sub CommandButton2_Click()
Dim TicketNum As String
Dim strCheck As Range
TicketNum = Workbooks("PensionDataCorrection.xls").Sheets(1).Range("C4")
With Workbooks("PensionDataCorrection.xls").Sheets(2).Range("b:b")
    On Error Resume Next
    strCheck = .Find(what:=TicketNum, After:=Cells(1, 1), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=ByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)
    MsgBox "Value is in column: " & strCheck
strCheck.Offset(0,3) = Date
    On Error GoTo 0
    End With
If strCheck <> vbNullString Then
    MsgBox "Does Not Exist"
    End If
End Sub

 

[AlphaFrog]

Private Sub CommandButton2_Click()

    Dim Found As Range
    
    With Workbooks("PensionDataCorrection.xls")
        If .Sheets(1).Range("C4") = "" Then
            MsgBox "Nothing entered in cell C4 to search for."
            Exit Sub
        End If
        
        Set Found = .Sheets(2).Range("B:B").Find(What:=.Sheets(1).Range("C4"), _
                                                 LookIn:=xlValues, _
                                                 Lookat:=xlWhole, _
                                                 SearchOrder:=xlByRows, _
                                                 SearchDirection:=xlNext, _
                                                 MatchCase:=False)
        If Not Found Is Nothing Then
            'Match found
            MsgBox "Value is in column: " & Found.Offset(0, 1).Value
        Else
            'Match Not Found
            MsgBox "Does Not Exist"
        End If
    End With
    
End Sub

 

[Naitnoum]

Thank you both AlphaFrog and Noz2k.

I tried both codes. Noz2k the code you provided has a runtime error with the following line:

If strCheck <> NullString Then

Do you think it might have to do with the "vbNullString"?

The code you provided AlphaFrog provides me with the column and not the row. I thought that perhaps I could just change SearchOrder:=xlByRows To perhaps SearchOrder:= xlByColumn and that did work for what are now pretty obvious reasons now.

Could either of you, or anyone else, also help me understand how I could update the cell in the fifth column of the row found? Noz2k, you stated tht I could simply use an offset to reference to the fifth row, but how does that work? If I state Found.Offset(0,5).value will the fifth column in the row designated by the variable "Found" be selected?

Thanks again to both of you. Your attention is really appreciated and all your help is fabulous in helping me understand vba better.

 

[AlphaFrog]

This will put today's date in in column E (5th column?) of the same row that the match is found in column B

Private Sub CommandButton2_Click()

    Dim Found As Range
    
    With Workbooks("PensionDataCorrection.xls")
        If .Sheets(1).Range("C4") = "" Then
            MsgBox "Nothing entered in cell C4 to search for."
            Exit Sub
        End If
        
        Set Found = .Sheets(2).Range("B:B").Find(What:=.Sheets(1).Range("C4"), _
                                                 LookIn:=xlValues, _
                                                 Lookat:=xlWhole, _
                                                 SearchOrder:=xlByRows, _
                                                 SearchDirection:=xlNext, _
                                                 MatchCase:=False)
        If Not Found Is Nothing Then
            'Match found
            'MsgBox "Value is in column: " & Found.Offset(0, 1).Value
            [COLOR="Red"].Sheets(2).Range("E" & Found.Row).Value = Date[/COLOR]
        Else
            'Match Not Found
            MsgBox "Does Not Exist"
        End If
    End With
    
End Sub

 

[Naitnoum]

Works like a charm! So essentially the key to my issue was

.sheets(2).Rage("G" & Found.Row).Value = Date

This will enter the date in the cells "value" on the row that equals the "Found" variable on the row "G" that I detemined. I get it! Thanks!

 

[AlphaFrog]

FYI: this would do the same

Found.Offset.(,5).Value = Date

Offsets 5 cells to the right from the found cell in column B