UserForm with 2 macros. One works and one doesn't!

[jmpatrick]

Happy Wednesday!

I'm working with a UserForm that I created (called frmSubMaintenance) that pulls information from a sheet (called DataCenter) and allows me to edit and save the results back to DataCenter. There are two command buttons with rather simple code that do the work. Here's the first one. It works as designed:

Private Sub cmdSearchSubName_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub

Here's the second. Almost the same code but it doesn't do anything:

Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 2).Value = SubCode Then
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub

Here's what DataCenter looks like. Sub Name is in column E and Sub Code is in column B.

When I use the cmdSearchSubName command button all the relevant information is pulled as desired.

When I use the cmdSearchSubCode command button nothing happens. No error no data.

Thoughts?

 

[jmpatrick]

Success! I'm searching one column (the one with 4-digits) but returning the value from another column (the one that may also include the "M"):

Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 3).Value = SubCode Then
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub

I didn't have to change any formulas or anything else and It seems to work. I'll test it thoroughly and report back.

 

[jmpatrick]

The only issue I see is this:

If you search for SubCode 1112 it correctly returns 1112M (as shown). However, if you delete the M and click Search By Sub Code it stays at 1112. It should return 1112M.

 

[Fluff]

As col B is numeric use

If Worksheets("DataCenter").Cells(i, 2).Value = Val(SubCode) Then

 

[jmpatrick]

As col B is numeric use

If Worksheets("DataCenter").Cells(i, 2).Value = Val(SubCode) Then

That line was changed to:

If Worksheets("DataCenter").Cells(i, 3).Value = SubCode Then

That was so it would scan column 3 (always 4-digit number) but return column 2 (with the M if present).

 

[Fluff]

There is nothing in your code that is looking at col 2 anymore

 

[jmpatrick]

There is nothing in your code that is looking at col 2 anymore

I still have a lot to learn! I thought this part was:

lastrow = Worksheets("DataCenter").Cells(Rows.Count, 2).End(xlUp).Row

 

[Fluff]

That's just working out where the last row is.
If you want the subcode returned you will need to add this

SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value

 

[jmpatrick]

That's just working out where the last row is.
If you want the subcode returned you will need to add this

SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value

That was it! Thanks, as always.

 

[jmpatrick]

So this was the code that works in case someone else is trying to do something similar:

Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 3).Value = SubCode Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub

 

[Fluff]

You're welcome & thanks for the feedback.