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]

btw: I also added a button and code to search by Sub Initials in column D. This also works fine.

 

[Fluff]

Are the values in col B text or actual numbers?

 

[jmpatrick]

They are formatted "General" the same as the Sub Name column.

 

[Fluff]

But was is in the cell? what does

=isnumber(B2)

return?

 

[jmpatrick]

But was is in the cell? what does

=isnumber(B2)

return?

When I put it in B1 it returns TRUE.

 

[jmpatrick]

OK, when I put '1119 in B1 instead of just 1119 the search finds it.

 

[jmpatrick]

When I switch the search column from 2 to 3 it works (see below). The issue with that is that there's a formula in column 3 that trims column 2 down to just a 4-digit number. Some SubCodes have an important "M" that follows the 4 digits. What I would need to happen if I search for 1119M with just 1119 is that the return would display correctly as 1119M. Right now if I search the SubCodes for 1119 I get back 1119. No "M". If the user clicks "Update Record" at this point the "M" is now gone. Make sense?

Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 3).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

 

[jmpatrick]

Is it possible to combine these in column 3?:

=LEFT($B1,4)

...and

="" & $B1

 

[jmpatrick]

Tried this in C1:

=LEFT($B1,4*(""&$B1))

Got this error when I ran a search:

Run-time error '13':

Type mismatch