CODEWITHSUNDEEP
vbaexcelsearchuserform
MBrann
MBrann

Reputation: 265

Search and Update Values in a row with a userform

I have created a userform for data entry. I am now looking to expand this to look into data that has already been entered, where some data may be missing, and updating the values within that row.

The search criteria is a reference number, which is in the table in column A. The code however is returning the msg that ID is not found. 

Dim searchRange As Range
Dim foundCell As Range
Dim mysearch As String

mysearch = Me.Reference.Value

Set searchRange = ThisWorkbook.Sheets("Master Data").Range("A2").End(xlDown)
Set foundCell = searchRange.Find(what:=mysearch, after:=searchRange.Cells(searchRange.Cells.Count))

If Not foundCell Is Nothing Then
    If foundCell.Offset(0, 1).Value = Me.Reference.Value Then
       foundCell.Offset(0, 8).Value = Me.Merchant.Value
    Else
        MsgBox "Name does not exist."
    End If
Else
    MsgBox "ID does not exist."
End If

Upvotes: 1

Views: 3615

Answers (1)

SJR
SJR

Reputation: 23081

Try this, your searchRange as defined is only a single cell which I assume is not what you intended. I have also specified a few of the Find parameters just in case they are not what you assumed them to be.

Sub x()

Dim searchRange As Range
Dim foundCell As Range
Dim mysearch As String

mysearch = Me.Reference.Value

With ThisWorkbook.Sheets("Master Data")
    Set searchRange = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
End With        

Set foundCell = searchRange.Find(what:=mysearch, Lookat:=xlwhole, MatchCase:=False, SearchFormat:=False)
If Not foundCell Is Nothing Then
     If foundCell.Offset(0, 1).Value = Me.Reference.Value Then
         foundCell.Offset(0, 8).Value = Me.Merchant.Value
     Else
         MsgBox "Name does not exist."
     End If
Else
     MsgBox "ID does not exist."
End If

End Sub

Upvotes: 3

Related Questions