Consulting

Results 1 to 3 of 3

Thread: if find less that six numeric value clean the cell

  1. #1
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location

    Question if find less that six numeric value clean the cell

    I need a VBA Code that check column D and if find less than six number value (six numeric value with out space) clean the data on that cell . For example

    In Column D Cell 1 i have these data :
    D1 :#S/N - 12862,S/N - 11546,SER NO 4890 -,SER NO 1706 -@ SER NO 1709 - (USA),SER NO

    In Column D Cell 2 I have these data
    D2 : SER NO 3064) -,SER NO 10765 -,SER NO 2597 -117367#

    When run the VBA it will clean data on Column D Cell 1 Because cannot find six numeric value . but in D2 there is one 117367 and it will not deleted .

    For example if i have data like this : 12345 6 or 1234-56 or 123456- also the cell will be clean numeric value must be without space or any special character .

  2. #2
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Public Sub Clean()
        For Each c In ActiveSheet.Range("d:d").Cells
            If c.Text = "" Then Exit For
            If Not chkNum(c.Text, 6) Then c.Formula = ""
        Next
    End Sub
    
    Private Function chkNum(s As String, concchar As Byte) As Boolean
        Dim n As Integer
        For i = 1 To Len(s)
            Select Case Asc(Mid(s, i, 1))
            Case 48 To 57
                n = n + 1
                If n = concchar Then Exit For
            Case Else: n = 0
            End Select
        Next
        chkNum = n >= concchar
    End Function

  3. #3
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    Thank you very much and my request its done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •