On Apr 12, 6:19*pm, Tom Hutchins
wrote:
If nothing else, you are using the variable "i" in one subroutine, then
referring to it in another subroutine. You need to either declare it as a
public variable or pass its value as a parameter to the second subroutine;
otherwise, the "i" in the second sub is not the same "i" as in the first sub.

Your code checks the length of the entry but does nothing to ensure that
only digits are entered. Try this bersion:

Option Explicit
Dim Rng As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim col As String
Dim i As Long

Sub ValidateData()
'validate values in a column
Range("N11").Select
Range(Selection, Selection.End(xlDown)).Select
Set Rng = Selection
FirstRow = Rng.Row
LastRow = Rng.Rows(Rng.Rows.Count).Row
col = "n"
'loop thru col n of database, but skip first row with headings
For i = FirstRow To LastRow
* * FixColumnB
Next i
End Sub

Private Sub FixColumnB()
Dim x As Integer, OK As Boolean
OK = True
'check to ensure six digits entered
If Len(Range("a1")(i, col).Value) < 6 Then
* * OK = False
End If
'check that only digits are entered
For x = 1 To Len(Range("a1")(i, col).Value)
* * Select Case Mid(Range("a1")(i, col).Value, x, 1)
* * Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
* * * * 'okay
* * Case Else
* * * * OK = False
* * End Select
Next x
If OK = False Then
'Enter a new value in Column B
* * Range("a1")(i, col).Select
* * Range("a1")(i, col).Value = vbNullString
* * Range("a1")(i, col) = InputBox("Enter a 6 digit value")
* * FixColumnB
End If
End Sub

Hope this helps,

Hutch



"Ron" wrote:
Hello All,

Trying to validate 6 characters in column *N. *This one used to work
but for some reason it's failing. *Has a inputbox for user to make
correction with. *I need to apply this to other columns with different
length requirements. *All assistance greatly appreciated. *Thanks, Ron

Sub ValdateData()
'validate values in a column
Range("N11").Select
Range(Selection, Selection.End(xlDown)).Select

Set Rng = Selection
FirstRow = Rng.Row
LastRow = Rng.Rows(Rng.Rows.Count).Row

Col = "n"
'loop thru col n of database, but skip first row with headings
For i = FirstRow To LastRow
* * If Len(Range("a1")(i, Col).Value) < 6 Then
* * * * FixColumnB
* * End If
Next i

End Sub

Sub FixColumnB()
'Enter a new value in Column B
* * Range("a1")(i, Col).Select
123456

Range("a1")(i, Col) = InputBox("Enter a 6 digit value")

* * 'check to ensure six digits entered
* * If Len(Range("a1")(i, Col).Value) < 6 Then
* * * * FixColumnB
* * End If

End Sub- Hide quoted text -

- Show quoted text -

Hi Tom, solution worked great. Thank you for correcting the code I
was using. I have another column to test cells for text length. Can
this code be altered to handle text as well as numeric? I really
appreciate your assistance, Ron