VBA语法（2）

一、判断语句

1. iF判断

//单条件判断
Sub 判断1()
    If Range("a1").Value > 0 Then
       Range("b1") = "A1的值是正数"
    Else
       Range("b1") = "A1的值是负数或者零"
    End If
End Sub


//多条件判断
Sub 判断2()
    If Range("a1").Value > 0 Then
       Range("b1") = "A1的值是正数"
     ElseIf Range("a1").Value = 0 Then
       Range("b1") = "A1的值是零"
     Else
       Range("b1") = "A1的值是负数"
      End If
End Sub

//多条件判断	
Sub 多条件判断2()
    If Range("a1") <> "" And Range("a2") <> "" Then
        Range("a3") = Range("a1") * Range("a2")
    End If
End Sub

tip :单元格的多条件判断不同

2.select 判断

//单条件判断
Sub select条件判断()
    Select Case Range("a1").Value
    Case Is > 0
        Range("b1") = "a1是正数"
    Case Else
        Range("b1") = "a2是负数或者0"
    End Select
End Sub

//多条件判断
Sub select多条件判断()
    Select Case Range("a1").Value
    Case Is > 0
        Range("b1") = "a1是正数"
    Case Is = 0
        Range("b1") = "a1是0"
    Case Else
        Range("b1") = "a1是负数"
    End Select
End Sub

3.IIF判断

Sub IFF条件判断()
    Range("a3") = IIf(Range("a1") <= 0, "负数或者0", "正数")
End Sub

4.区间判断

Sub IF区间条件判断()
    If Range("a1") < 1000 Then
        Range("b1") = 1
     ElseIf Range("a1") < 2000 Then
        Range("b1") = 2
     ElseIf Range("a1") >= 2000 Then
        Range("b1") = 3
    End IF  
End Sub

Sub Select区间条件判断()
   Select Case Range("a1").Value
    Case 0 To 1000
        Range("a2") = 1
          Case 1001 To 2000
        Range("a2") = 2
          Case Is > 2000
        Range("a2") = 3
     End Select
    
End Sub

二、循环语句

1.for循环

Sub for循环语句()

    Dim x As Integer
    For x = 2 To 6
        Range("d" & x) = Range("b" & x) * Range("c" & x)
    Next x
End Sub

2.for Each 循环

Sub forEach循环语句()
    Dim rg As Range
    For Each rg In Range("d2:d5")
    //offset(x,y) 当y为正数时表示当前对象右边第Y个单元，为负数则反之
        rg = rg.Offset(0, -1) * rg.Offset(0, 1)  
    Next rg
End Sub

3.do循环

使用 exit do 可以跳出一次循环

Sub do循环语句()
    Dim x As Integer
    x = 1
    Do
    x = x + 1
    Cells(x, 4) = Cells(x, 2)
    Loop Until x = 10
End Sub

Sub dowhile循环语句()
 Dim x As Integer
    x = 1
    Do While x < 10
    x = x + 1
    Cells(x, 4) = Cells(x, 2)
    Loop
End Sub