Excel Forum

Code: Select all

Public Sub Adjust()
    Dim Ans, PiNo(100), CelVal As Variant
    Dim FirstRow, i, j, k, l As Integer

    FirstRow = 1
    i = 1
    k = 100
    
    While k > 0
          k = k - 1
          i = i + 1
          
          CelVal = Cells(FirstRow + i, 2).Value
          
          If CelVal <> 0 And Len(CelVal) > 7 Then
             ' Reset k while have data row
             k = 100
             j = 0
             ' Create each PiNo
             j = j + 1
             PiNo(j) = ""
             
             For l = 1 To Len(CelVal)
                 If Asc(Mid(CelVal, l, 1)) <> 10 And Asc(Mid(CelVal, l, 1)) <> 32 Then
                    PiNo(j) = PiNo(j) & Mid(CelVal, l, 1)
                 Else
                    j = j + 1
                    PiNo(j) = ""
                 End If
             Next l
             
             Ans = ""
             
             For l = 1 To j
                  Ans = Val(PiNo(l))
             Next l
             Cells(FirstRow + i, 4).Value = Ans
          
          
          End If
    Wend

End Sub