About Us

Alberto Ast[_2_]

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
---------------
Jacob Skaria

"Alberto Ast" wrote:

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?

Alberto Ast[_2_]

Thanks... you got them all right

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"Alberto Ast" wrote:

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?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Assigning a Variable to an Expression that Includes a Variable andVBA Property	bluebird[_3_]	Excel Programming	3	April 27th 09 07:38 AM
Nothing Keyword Destories Objects rather than just resetting the variable to an empty variable	Ronald R. Dodge, Jr.[_2_]	Excel Programming	15	December 15th 08 09:19 PM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?	Daniel	Excel Worksheet Functions	1	July 9th 05 03:05 AM
Run-time error '91': "Object variable or With block variable not set	Mike[_92_]	Excel Programming	2	December 30th 04 10:59 AM
Cells.Find error Object variable or With block variable not set	Peter[_21_]	Excel Programming	2	May 8th 04 02:15 PM

Menu

About Us