PDA

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



markpem
05-12-2015, 08:35 AM
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
05-12-2015, 09:19 AM
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
05-13-2015, 05:50 AM
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
05-13-2015, 07:22 AM
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
05-13-2015, 08:29 AM
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