VBA 中 do-loop,do-while-loop,do-until-loop,for-each-next 的小例子

165 阅读 0 评论 109 点赞

我是靠谱客的博主飘逸钻石，这篇文章主要介绍VBA 中 do-loop,do-while-loop,do-until-loop,for-each-next 的小例子，现在分享给大家，希望可以做个参考。

do-loop	Sub doloop1()
	Dim a%
	Do While a > 10
	a = a + 1
	MsgBox "a"
	Loop
	End Sub


	Sub doloop2()
	Dim a%
	Do
	a = a + 1
	If a > 10 Then
	MsgBox a & ">10"
	Exit Do
	End If
	Loop
	End Sub


	Sub doloop3()
	Dim a%
	Do While a <= 10
	a = a + 1
	Loop
	MsgBox a & ">10"
	End Sub

	Sub doloop4()
	Dim a%
	Do While a < 10
	a = a + 1
	Loop
	MsgBox a & ">10"
	End Sub

	1~11行，（rs,2）>90的画o
	Sub doloop5()
	Dim rs%
	rs = 1
	Do
	rs = rs + 1
	If rs >= 11 Then
	Exit Do
	Else
	If Cells(rs, 2) >= 90 Then Cells(rs, 3) = "o"
	End If
	Loop
	End Sub

do-while-loop	从18行一直到结束，大于100的画圈
	Sub dowhileloop1()
	Dim rs%
	rs = 18
	Do While Cells(rs, "G") <> ""	直到单元格不为空时，循环
	If Cells(rs, "G") >= 100 Then Cells(rs, "I") = "0"
	rs = rs + 1
	Loop
	End Sub

	从18行一直到结束，大于100的画圈
do-until-loop	Sub dountilloop1()
	Dim rs%
	rs = 18
	Do Until Cells(rs, "g") = ""	直到单元格为空时，才结束
	If Cells(rs, "g") >= 90 Then Cells(rs, "i") = "x"
	rs = rs + 1
	Loop
	End Sub


	隔行填色
	Sub dountilloop2()
	Dim rs%
	rs = 2
	Do Until Sheet1.Range("a" & rs) = ""
	Sheet1.Range("a" & rs & ":" & "g" & rs).Interior.ColorIndex = 7
	rs = rs + 2
	Loop
	End Sub


	有时再循环的最后一行进行判断，更具有意义
	输入密码，超过三次则退出
	Sub dountilloop3()
	Dim pass$, i!
	Do
	i = i + 1
	If i > 3 Then
	Exit Do
	End If
	pass = InputBox("pass = ")
	Loop Until pass = "123"
	End Sub


for-each-next	将a2到a10中符合条件的表格填充为红色
	Sub foreachnext1()
	Dim rng As Range, n!
	For Each rng In Sheet1.Range("a2:a10")			在A2到A10的范围内的每一个单元格
	If rng = "a5" Then rng.Interior.ColorIndex = 3
	Next
	End Sub

	读取当前所有的工作表
	Sub foreachnext()
	Dim wsh As Worksheet, n As Byte, m As String
	For Each wsh In Worksheets
	n = n + 1
	m = wsh.Name		m就可省略了
	Sheet1.Cells(n, "g") = m		可以合成一句Sheet1.Cells(n, "g") = wsh.Name
	Next
	End Sub


	用for循环算Cells(rng, "g") * Sheet1.Cells(rng, "h")的长度
	Sub foreach2()
	Dim rng!
	For rng = 18 To 31 Step 1
	Sheet1.Cells(rng, "j") = Sheet1.Cells(rng, "g") * Sheet1.Cells(rng, "h")
	Next
	End Sub