Validata a variable

[Alberto Ast]

I need to validate a cell contains only numbers from the 3rd position to the
forth. I have below statement to convert to number but if there is an
alfanumeric value the macro fails.

myVal = Mid(wb.Sheets("AOS Form").Range("Q2").Value, 3, 4) * 1

How do I do it?

 

[Jacob Skaria]

Hi again..

--You can use IsNumeric to check whether this part is numeric..
--If you mean 3rd position to the forth which mean 2 positions this should be
mid(string,3,2) instead of mid(string,3,4)


Dim myval As Variant
If IsNumeric(Mid(wb.Sheets("AOS Form").Range("Q2").Value, 3, 4)) Then
myVal = Mid(wb.Sheets("AOS Form").Range("Q2").Value, 3, 4) * 1
End If

'OR

Dim myval As Variant
With wb.Sheets("AOS Form")
If IsNumeric(Mid(.Range("Q2").Value, 3, 4)) Then
myVal = Mid(.Range("Q2").Value, 3, 4) * 1
End If
End With



If this post helps click Yes

 

[Alberto Ast]

Thanks... you got them all right

Hi again..

--You can use IsNumeric to check whether this part is numeric..
--If you mean 3rd position to the forth which mean 2 positions this should be
mid(string,3,2) instead of mid(string,3,4)


Dim myval As Variant
If IsNumeric(Mid(wb.Sheets("AOS Form").Range("Q2").Value, 3, 4)) Then
myVal = Mid(wb.Sheets("AOS Form").Range("Q2").Value, 3, 4) * 1
End If

'OR

Dim myval As Variant
With wb.Sheets("AOS Form")
If IsNumeric(Mid(.Range("Q2").Value, 3, 4)) Then
myVal = Mid(.Range("Q2").Value, 3, 4) * 1
End If
End With



If this post helps click Yes