Formula doesn't work without ' in front of numbers?

[jmpatrick]

This has been causing problems in several areas of my spreadsheet.

This formula will not work unless the number in column C is preceded by a single quotation mark:

=IF(C54="1","",IF(C54="2","Duplex",IF(C54="3","3-Unit",IF(C54="4","4-Unit",IF(C54="5","5-Unit",IF(C54="6","6-Unit",""))))))

However, the single quotation mark breaks all my code like this:

Private Sub cmdSearchModelCode_Click()
    Dim LastRow     As Long
 
    ModelCode = Trim(ModelCode.Text)
    If Len(ModelCode.Text) = 0 Then Exit Sub
 
    With wsModels
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = ModelCode Then
                ModelCode.Text = .Cells(i, 1).Value
                ModelName.Text = .Cells(i, 2).Value
                ModelUnits.Text = .Cells(i, 3).Value
    
             Me.cmdDeleteModel.Enabled = True
            Exit For
        End If
    Next
 End With
End Sub

Here's the formula not working in Column D (no single-quotation preceding the 2 in C2):

Here's the formula working correctly in Column D (with single-quotation preceding the 2 in C2). This works here but breaks the code above:

I've tried formatting the cell as text, formatting general and formatting as a number.

What am I doing wrong?

 

[jmpatrick]

Assuming your codes actually are numeric (Fluff's comment suggests not), you'd add:

Dim ModelCode as Long

or change any existing variable declaration to Long.

Some of the values include a forward slash.

 

[RoryA]

In that case you'll need to convert only the numbers:

Dim ModelCode as Variant
ModelCode = Trim(ModelCode.Text)
If Isnumeric(ModelCode) then modelcode = CLng(modelcode)

 

[jmpatrick]

In that case you'll need to convert only the numbers:

Dim ModelCode as Variant
ModelCode = Trim(ModelCode.Text)
If Isnumeric(ModelCode) then modelcode = CLng(modelcode)

Changed it to this:

Private Sub SearchModelCode_Click()
 
    Dim ModelCode As Variant
    ModelCode = Trim(ModelCode.Text)
    If IsNumeric(ModelCode) Then ModelCode = CLng(ModelCode)
    If Len(ModelCode.Text) = 0 Then Exit Sub
 
    With wsModels
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = ModelCode Then
                ModelCode.Text = .Cells(i, 1).Value
                ModelUnits.Text = .Cells(i, 3).Value
                ModelName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub

Getting an error: Run-time error '424': Object required

I doesn't seem to like this: ModelCode = Trim(ModelCode.Text)

 

[RoryA]

Sorry - I had overlooked that you have a control by that name. Use:

Private Sub SearchModelCode_Click()
If Len(ModelCode.Text) = 0 Then Exit Sub
Dim theModelCode as Variant
theModelCode = Trim(ModelCode.Text)
If Isnumeric(theModelCode) then themodelcode = CLng(themodelcode)
    With wsModels
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = theModelCode Then
                ModelCode.Text = .Cells(i, 1).Value
                ModelUnits.Text = .Cells(i, 3).Value
                ModelName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub

 

[jmpatrick]

Sorry - I had overlooked that you have a control by that name. Use:

Private Sub SearchModelCode_Click()
If Len(ModelCode.Text) = 0 Then Exit Sub
Dim theModelCode as Variant
theModelCode = Trim(ModelCode.Text)
If Isnumeric(theModelCode) then themodelcode = CLng(themodelcode)
    With wsModels
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = theModelCode Then
                ModelCode.Text = .Cells(i, 1).Value
                ModelUnits.Text = .Cells(i, 3).Value
                ModelName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub

Works perfect. What did we just do?

 

[RoryA]

It converts numeric values to a real number before comparing them to the cell values.

 

[jmpatrick]

It converts numeric values to a real number before comparing them to the cell values.

Always learning thanks to this site. Thanks to both of you.