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

ln = LEN(Range("a1")(i, col).Value)
if ln=6 OR ln=0 then
//do something
end if

"Ron" wrote:

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

"Ron" wrote:
How would I edit this line to allow only
a blank cell or 6 digits.
If Len(Range("a1")(i, col).Value) < 6 Then

Depends on what you mean by "blank cell": empty, or empty or a null string
(""). One of the following might work for you:

If Range("a1")(i,col) = "" or Len(Range("a1")(i,col)) = 6 Then

If IsEmpty(Range("a1")(i,col)) Or Len(Range("a1")(i,col)) = 6 Then

Note-1: You said "allow ... 6 digits". That is why I changed "< 6" to "=
6". If "< 6" is "allow" in your parlance, then I don't know quite what you
mean by "allow a blank cell". You might need to reverse the above logic
using "Not IsEmpty(...)".

Note-2: If by "blank cell", you also want to allow for a string of spaces
(e.g. " "), you can use Trim(Range("a1")(i,col)) in the first solution.

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

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

What was wrong with yesterday's solution?

On Apr 17, 3:49*pm, "Harald Staff" wrote:
What was wrong with yesterday's solution?

Hi Harald, I worked with your code but, really don't understand how
to incorporate it into the code I'm using. When I ran the code on a
selection of cells with a few cells that needed attention it didn't
recognize them or select them for correction. An Inputbox comes up and
asks for 6 digits, I enter 6 digits click OK, input box goes away,
nothing is entered in the worksheet from the Inputbox. Also, my
requirements changed. Now a blank or "" cell is to be ignored. So,
cells with "" or, blank or, 6 digits which ever is the case is to be
ignored. Cells with other entries need to be corrected. In the code
I'm using ValidateDataN selects and cycles through the data to
validate and FixColumnN is to fix the cells with data that does not
meet the requirements. Also, the cancel button does not cancel the
process and I have to quit (Ctrl Alt Del) Excel to get out of the
process. I'm interested in your approach on how to handle this
situation and also, interested in understanding what your code was
intended to do. Thank you for your assistance. Ron (rookie at work)

Okay, try your If statement this way instead...

If Not IsEmpty(Cells(i, col)) Or Len(Cells(i, col)) < 6 Then

--
Rick (MVP - Excel)


"Ron" wrote in message
...
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

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