excel vba to loop through cells and fix via an input box

[BORUCH]

hi all

i have this below excel sheet

Book1
	A	B	C
1	item #	quantity	description
2	12345	2	apples
3	01000	4	banana
4	01001,55555	5	oranges
5	01002	6	kiwis
6	01003	8	watermelon
7	01004,66666	10	pears
Sheet1

i would like an excel VBA that loops through column A and finds any cell that is greater then 5 digits if it finds it i want an input box to be able to change it and the input box should only accept 5 digits numeric values

thanks

 

[igold]

Perhaps something like this-

Sub fivedig()

    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim rng As Range, cel As Range
    
    Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        For Each cel In rng
            If IsNumeric(cel) Then
                Do Until Len(cel) = 5
                    If Not Len(cel) = 5 Then
                        cel = InputBox("The currrent Cell is->   " & cel & "   <-Please correct this so that it is a 5 digit number.")
                        If cel = vbNullString Then Exit Sub
                    End If
                Loop
        End If
    Next
    
End Sub

 

[Akuini]

Another option:

Sub BORUCH()
Dim c As Range, sVar As String
    For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
       If Not c Like "#####" Then
           Do
               sVar = Application.InputBox("Insert 5 digit:", Type:=2)
               
               If sVar = False Then Exit Sub  'if user cancel
                   If Not sVar Like "#####" Then
                       MsgBox "Incorrect ... "
                   Else
                       c = sVar
                       Exit Do
                   End If
           
           Loop While Not sVar Like "#####"
        End If
    Next
End Sub

 

[BORUCH]

thanks all for the code