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?

 

[Fluff]

You need to remove the quotes from the numbers, in that formula.

Also what version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jmpatrick]

You need to remove the quotes from the numbers, in that formula.

Also what version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Account details updated! I'm using Office 365 and Windows 10.

Removing the quotes doesn't work. It now displays the formula in column D.

 

[Fluff]

Thanks for that, you could also rewrite your formula like

=IF(C54=2,"Duplex",IF(AND(C54>=3,C54<=6),C54&"-Unit",""))

 

[jmpatrick]

Time to revisit this one. Here's where I'm at:

Private Sub SearchModelCode_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
                ModelUnits.Text = .Cells(i, 3).Value
                ModelName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub

Column 1 is formatted as General and consists of numbers. When I run the code above I get no matching. However, when the values in Column 1 are preceded by a ' it works.

I don't know if my code is wrong or the formatting of the cells in Column 1 is wrong.

 

[RoryA]

Declare ModelCode as Long

 

[Fluff]

Are you now saying the formula doesn't work, or the code doesn't, or both?

Column 1 is formatted as General and consists of numbers.

1559/1559 is not a number it's text.

 

[jmpatrick]

Are you now saying the formula doesn't work, or the code doesn't, or both?

1559/1559 is not a number it's text.

The code.

 

[jmpatrick]

Declare ModelCode as Long

Not sure how to do that.

 

[RoryA]

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.