Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Cell Character Length with VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Cell Character Length with VBA
Try changing this:
If Len(Range("a1")(i, Col).Value) < 6 Then To This: If Len(Cells(i, Col).Value) < 6 Then "Ron" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Cell Character Length with VBA
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 "Ron" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Cell Character Length with VBA
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Cell Character Length with VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Predefined Cell Character Length | Excel Worksheet Functions | |||
deleting a cell based on character length? | Excel Programming | |||
assigning character length in a cell | Excel Discussion (Misc queries) | |||
How do I limit the character length of a cell when typing | Excel Programming | |||
length of character data | Excel Discussion (Misc queries) |