Validate Cell Character Length with VBA

[Ron]

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

 

[JLGWhiz]

Try changing this:

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

To This:

If Len(Cells(i, Col).Value) <> 6 Then

 

[JLGWhiz]

I didn't test this but it should do the job for you. I you get any errors
post back. Just copy it into your main code module.

Sub chkLen()
Dim lr As Long, c As Range
lr = ActiveSheet.Cells(Rows.Count, 14).End(xlUp).Row
Set srcRng = ActiveSheet(Range("N2:N" & lr)
For Each c In srcRng
If Len(c.Value) <> 6 Then
FixColumnB
End If
Next
End Sub

 

[Tom Hutchins]

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]

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














- 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