VBA to check number of digits in cell

[boliver228]

Hi,

I have a macro which submits a spreadsheet to an excel site. The macro checks that a number of compulsory fields are populated before it allow this spreadsheet to be submitted, and if any checks fail, it displays a message box and exits the macro.

One of the checks is to make sure a cell [CRM_Reference] contains either a 6 or 9 digit integer. I've tried the code below but it doesn't work

Dim Chars1 as Integer
Dim Chars2 as Integer
Chars1= 9
Chars2 = 9
If Len(Range("CRM_Reference").Text)) <> Chars 1 Or Len(Range("CRM_Reference").Text)) <> Chars 2 Then
MsgBox "Insert Message Here"
Exit Sub
End If

Any ideas why not?

 

[mikerickson]

Perhaps

If cellText Like "######" Or cellText Like "#########" Then 
    Msgbox "good integer"
End If

 

[boliver228]

Perhaps

If cellText Like "######" Or cellText Like "#########" Then 
    Msgbox "good integer"
End If

Thanks but this only works for the 6 digits. Code below:

If Not Range("CRM_Reference").Text Like "######" Or Range("CRM_Reference").Text Like "#########" Then

Any ideas?

 

[pgc01]

Hi

You forgot the parentheses:

If Not (Range("CRM_Reference").Text Like "######" Or Range("CRM_Reference").Text Like "#########") Then

 

[boliver228]

Hi

You forgot the parentheses:

If Not (Range("CRM_Reference").Text Like "######" Or Range("CRM_Reference").Text Like "#########") Then

Perfect, thanks a lot