【Excel VBA】For...Next Counter Loop

https://docs.microsoft.com/zh-cn/dotnet/visual-basic/language-reference/statements/for-next-statement

1. Simple Loop

If I come across a negative value then leaves the loop

从后往前loop backward

2. Nested loop

两个Loop:
(1). 先在每个cell里循环，找出Text和number的部分=》例如row 1, 分别是AT \ 1030
(2). 再每行循环

Sub For_Next_Loop_in_Text()
    Dim i As Long 'for looping inside each cell
    Dim myValue As String
    Dim NumFound As Long
    Dim TxtFound As String
    Dim r As Long 'for looping through rows
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    ' outside loop, 每行循环
    For r = StartRow To LastRow
        myValue = Range("A" & r).Value
        ' inside loop，从i=1到len(myvalue)每个字符循环，如果是number，则+=到NumFound
        For i = 1 To VBA.Len(myValue)
            
            If IsNumeric(VBA.Mid(myValue, i, 1)) Then
                NumFound = NumFound & Mid(myValue, i, 1)
            
            ElseIf Not IsNumeric(Mid(myValue, i, 1)) Then
                TxtFound = TxtFound & Mid(myValue, i, 1)
            End If
            
        Next i
        ' 将number和text的部分分别赋值到不同cell
        Range("H" & r).Value = TxtFound
        Range("I" & r).Value = NumFound
        ' 每行loop后，存number和text的变量归零
        NumFound = 0
        TxtFound = ""
        
    Next r
    
End Sub

Mid()
函数返回给定输入字符串中指定数量的字符。
语法Mid(String,start[,Length])

Private Sub Constant_demo_Click()
       Dim var as Variant
       var = "Microsoft VBScript"
       msgbox("Line 1 : " & Mid(var,2))
       msgbox("Line 2 : " & Mid(var,2,5))
       msgbox("Line 3 : " & Mid(var,5,7))
End Sub

执行以上代码，得到：

Line 1 : icrosoft VBScript
Line 2 : icros
Line 3 : osoft V