IF and Then range based upon number rather than string - code inside. Help!

[markpem]

Hello

I currently have some code which runs through the whole of the used range

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 2 To lastRow

Currently it scans for certain keywords on the sheet like this:-

If Range("M" & x).Value = "AUG" Or Range("M" & x) = "SEP" Then

However I would like to do certain things based on a number rather than a string such as something like this:-

If Range("P" & x).Value <30 Then
If Range("P" & x).Value >30 and Range("P" & x).Value <60 Then

However I am getting a mismatch.

Could anyone help me out with the code I need? I guess I need to convert it from a string to an int?

Thanks

M

[Yongle]

I am not convinced that your problem is caused by a string/integer mismatch but here is some code that takes the values in column B and places them on same row in column E converted to numbers

Sub ConvertTextToNumber()
Dim i As Integer
For i = 1 To Range("A1").CurrentRegion.Rows.Count
Cells(i, 5).Value = CDec(Cells(i, 2).Value)
Next i
End Sub

Adapt it to test your theory.

BTW - The condition of your if statement cannot be satisfied - the value in the cell cannot be both < 30 and >30

If Range("P" & x).Value <30 Then
If Range("P" & x).Value >30.......

[markpem]

Hello Yongle

Thanks for your response. The AND operator in my <30 and >30 was a mistype and It's actually OR as you probably had guesses.

Regarding the integer/string mismatch before trying your code I opened up a spreadsheet and in column A went down each row from counting from 1-50 (so A1 had 1 and A30 had 30 in it)
I had a script which said if number <30 then add 1
then a messagebox to say the number counted under 30
this worked fine so it's something in my spreadsheet.

I tried your code and it failed on:-

Cells(i, 5).Value = CDec(Cells(i, 2).Value)

I actually think there must be something with the column but they are numbers only, there is no formulas or any conditional formatting so I'm stuck. My original code did work but not on the current spreadsheet I have.

Bizarre

Thanks Anyway for all your time and trouble

I am not convinced that your problem is caused by a string/integer mismatch but here is some code that takes the values in column B and places them on same row in column E converted to numbers

Sub ConvertTextToNumber()
Dim i As Integer
For i = 1 To Range("A1").CurrentRegion.Rows.Count
Cells(i, 5).Value = CDec(Cells(i, 2).Value)
Next i
End Sub

Adapt it to test your theory.

BTW - The condition of your if statement cannot be satisfied - the value in the cell cannot be both < 30 and >30

[Yongle]

Gald to help.
Like you say, very bizzare, but likely caused by something that Excel sees that is not immediately obvious.
It is easy to waste a lot of time puzzling this out.
Try creating a new worksheet, pasting "values" there, rename the original sheet, rename the new sheet with old original sheet name, and run the code again. If this works then delete the original sheet.

[Paul_Hossler]

I usually end up using a combination of IsNumeric, etc. and VarType

VarType would handle the cases (Long, Integer, Double, etc.) but for some reason I'm in the habit of IsNumeric

Option Explicit
Sub test()
    
    'just to set some test data
    ActiveSheet.Range("A1").Value = 12345
    ActiveSheet.Range("A2").Value = "SEP"
    ActiveSheet.Range("A3").Value = #5/1/2015#
    ActiveSheet.Range("A4").Value = CVErr(xlErrNA)
    ActiveSheet.Range("A5").Value = "Mary had a little lamb"
    
    
    MsgBox DemoFunction(ActiveSheet.Range("A1").Value)
    MsgBox DemoFunction(ActiveSheet.Range("A2").Value)
    MsgBox DemoFunction(ActiveSheet.Range("A3").Value)
    MsgBox DemoFunction(ActiveSheet.Range("A4").Value)
    MsgBox DemoFunction(ActiveSheet.Range("A5").Value)
    
End Sub

Function DemoFunction(V As Variant) As String
    If IsDate(V) Then
        DemoFunction = "It is a Date = " & Format(V, "mmmm yyyy")
    ElseIf IsNumeric(V) Then
        DemoFunction = "It is a Number = " & Format(V, "#,##0.00")
    ElseIf VarType(V) = vbError Then
        DemoFunction = "It is an ERROR"
    Else
        Select Case V
            Case "JAN"
                DemoFunction = "First Month"
            Case "SEP"
                DemoFunction = "Ninth Month"
            Case Else
                DemoFunction = "Some other kind of string = " & V
        End Select
    End If
End Function