Testing a single cell range for a number

[pglufkin]

I have a range variable called 'DataVar' which is always a single cell range. DataVar is defined as a range.

I want to test to see if the first character in the cell DataVar represents is a number like thus:

If IsNumeric(Left(DataVar.Text, 1)) = True Then
DataVar.Copy

I am getting the error 'Object variable or With block variable not set' because I think DataVar is defined as a range. However this works if 'Activecell' takes the place of 'DataVar'

I just want to see if the contents of DataVar's first character is a number. Thank you.

 

[dahmour]

make sure of using SET

Dim DataVar As Range
set DataVar = [a1]

 

[pglufkin]

Thank you, I had assigned the range through excel, but I guess you have to assign it through VBA. Thank you.

make sure of using SET

Dim DataVar As Range
set DataVar = [a1]

 

[Rick Rothstein]

Thank you, I had assigned the range through excel, but I guess you have to assign it through VBA. Thank you.

You said in your original post that you "have a range variable called 'DataVar' which is always a single cell range. DataVar is defined as a range"... how do you declare a range variable as a Range in "Excel" when variables are a VBA entity? Did you mean you created a Defined Name in Excel for that cell? If so, then you would not need a variable in VBA as you can reference the Defined Name directly from your code through the Range object. Assuming that is what you meant, then your If..Then test should look like this...

If IsNumeric(Left(Range("DataVar").Text, 1)) = True Then
Range("DataVar").Copy

I would point out that another way to test if the first character is a digit is like this...

If Range("DataVar").Text Like "#" Then
Range("DataVar").Copy

 

[pglufkin]

Thank you, I now see the distinction with defining a name in excel and setting a range in VBA. I like defining the name in Excel, eliminates a step in VBA.

You said in your original post that you "have a range variable called 'DataVar' which is always a single cell range. DataVar is defined as a range"... how do you declare a range variable as a Range in "Excel" when variables are a VBA entity? Did you mean you created a Defined Name in Excel for that cell? If so, then you would not need a variable in VBA as you can reference the Defined Name directly from your code through the Range object. Assuming that is what you meant, then your If..Then test should look like this...

If IsNumeric(Left(Range("DataVar").Text, 1)) = True Then
Range("DataVar").Copy

I would point out that another way to test if the first character is a digit is like this...

If Range("DataVar").Text Like "#" Then
Range("DataVar").Copy