On Apr 17, 3:32*pm, Ron wrote:
On Apr 17, 11:55*am, "Rick Rothstein"



wrote:
Two things. First, this...

* * Range("a1")(i, col).Value

can be written more clearly (in my opion) like this...

* * Cells(i, col).Value

The standard way to test this as you would be to do it similar to what
Patrick posted (first changing your Range("a1") to Cells as shown above).
Another way would be like this...

* * If Abs(Len(Cells(i, col).Value)) - 3) = 3 Then
* * * *'
* * * *' *The value is either 0 or 6 characters
* * * *' *long, so execute your code here
* * * *'
* * End If

--
Rick (MVP - Excel)

"Ron" wrote in message

....

Hi all,

How would I edit this line to allow only a blank cell or 6 digits.

If Len(Range("a1")(i, col).Value) < 6 Then

Thank you for your assistance, Ron- Hide quoted text -

- Show quoted text -

Hi guys,
This is the code I'm working with and having a few problems with. *One
problem is getting the input box to respond properly by recognizing
that a cell with no data or if there is data that it is <6 characters
is to be ignored. *If, it has less than 6 or more than 6 *the inputbox
asks for the correct input. Second problem is getting the cancel
button to cancel the process. *The way it's working now is that blank
or empty cells are being recognized but if it has any data it's asking
for the correct input and gets in a loop on the first cell with data
even if the data meets the correct requirement. *Cancel button does
nothing. *Assistance is greatly appreciated, Ron

Sub ValidateDataN()
'validate values in a column
Range("e12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 9).Select

Set Rng = Selection
FirstRow = Rng.Row
LastRow = Rng.Rows(Rng.Rows.Count).Row
col = "n"
'loop thru col n of database.
For i = FirstRow To LastRow
* * FixColumnN
Next i

End Sub

Private Sub FixColumnN()
Dim x As Integer, OK As Boolean
OK = True
'check to ensure cell is "" or has six digits entered
If IsEmpty(Range("a1")(i, col)) Or Len(Range("a1")(i, col)) = 6 Then
* * OK = False
End If

If OK = False Then
'Enter a new value in Column N
* * Range("a1")(i, col).Select
* * * * Range("a1")(i, col) = InputBox("Please enter a 6 digit value
in *" & ActiveCell.Address & ", *thank you.")

123 * *FixColumnN
End If

Rng.NumberFormat = "@"

End Sub- Hide quoted text -

- Show quoted text -

Hi all,
I've worked out a resolution. All of you suggestions were viable but,
the problem was on my side. I did not have the OK Boolean set
properly. Once I thought the process through I realized that there
was no actions set if OK was true. Here's the solution that I finally
used. I think all suggestions would work with the OK Boolean set
properly. Thank you all for your assistance I learned a lot from you
guys.
Private Sub FixColumnN()
Dim x As Integer, OK As Boolean
OK = False
'check to ensure activecell is "" or six digits entered,
'exceptions handled with the inputbox below.
If Range("a1")(i, col) = "" Or Len(Range("a1")(i, col)) = 6 Then

OK = True
End If

If OK = False Then
'Enter a new value in Column n
Range("a1")(i, col).Select
Range("a1")(i, col) = InputBox("Please enter a 6 digit value
in " & ActiveCell.Address & ", thank you.")

123 FixColumnN

End If

Rng.NumberFormat = "@"

End Sub