VBA Form Help

[TDC21]

I am trying to create form to update a table. The goal is to search by Template ID and add a MTR code in the corresponding row. It is a simple form but my VBA is pretty simple to. What I have is based on online samples that I have tried to modify to my data. With that said, not sure if I am missing something simple or if I am going about this entirely wrong.

If anyone can offer some advice it will be greatly appreciated.

Form has only 3 controls, text box "SearchTemplate", text box "Mtr", and a command button "CommandButton1"

My table data begins on row 8 and the column which contains the Template ID is "I", the column congaing the MTR is "J" . The code I have associated with the command button is as follows,

Private Sub CommandButton1_Click()


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


mysearch = Me.SearchTemplate.Value


With ThisWorkbook.Sheets("Sheet2")
    Set searchRange = .Range("I8", .Range("I" & .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.SearchTemplate.Value Then
         foundCell.Offset(0, 2).Value = Me.Mtr.Value
     Else
         MsgBox "Name does not exist."
     End If
Else
     MsgBox "ID does not exist."
End If


End Sub

When I run the search it processes but it but returns the "Name does not exist." prompt.

 

[Fluff]

What is this line meant to be doing?

 If foundCell.Offset(0, 1).Value = Me.SearchTemplate.Value Then

At the moment you find the SearchTemplate value in col I & then you check if the same value is in col J on the same row.

 

[TDC21]

The offsets were the part I understood least but I was thinking that was a prompt to say if foundCell = the data entry in the SearchTemplate txt box then to set the value for txt box Mtr = the corosponding row as FoundCell in column J.

With that said, my VBA is beginner level and most of this code was copied and edited so it is probably more accurate to say that is what I was hoping that line was doing.

 

[TDC21]

Based on what you said I tried replacing with,


If foundCell = Me.SearchTemplate.Value Then
foundCell.Offset(0, 1).Value = Me.Mtr.Value


Still getting the Name dose not exist prompt

 

[Fluff]

Try

If Not FoundCell Is Nothing Then
   FoundCell.Offset(0, 2).Value = Me.Mtr.Value
Else
   MsgBox "ID does not exist."
End If

 

[TDC21]

Awesome, thanks for the explanations and the help

 

[Fluff]

Glad to help & thanks for the feedback