在Excel的VBA宏中创建循环？ [英] Creating a Loop in VBA Macro for Excel?

问题描述

我很幸运地得到了某人对我以前提出的问题的回复。 请参阅此处。

只有我正在努力的事情是创建一个循环，结束宏在B列中的一个空白单元格。我一直在尝试阅读其他代码，但没有什么是真的点击我（如果，比循环，结束循环等）。这是我的宏的最后一步，我会喜欢关于如何为我创建循环和/或解决这个问题的任何资源。一如既往地感谢这个网站上的人是非常有用的！

  Sub Redirect（）
 Dim IE As Object 
 Dim doc As Object 
 
设置IE = CreateObject（InternetExplorer.Application）
 
与IE 
 .Visible = True 
。导航https：/ / ....
 
 Do Until .ReadyState = 4：DoEvents：Loop 
 
 With .Document.forms（digiSHOP）
 .elements（ old =（$）$）$ = $（$）
 .elements（NewUrl）Value = Range（B2）
 .submit 
结束
 
 Do Until .ReadyState = 4：DoEvents：Loop 
 Do While .Busy：DoEvents：Loop 
 
 End with 
 End Sub 
  

解决方案

虽然您可能希望循环直到空白行，但大多数人发现它更有用循环到特定列中的最后一个填充行，即使您在列中有几个（或多个）空格也可以工作。

请注意，您甚至可能不会需要一个 循环。使用 .autofilter 或 .find 更有效率。 （相当于在SQL查询中使用where子句而不是游标）

如果你需要循环，最好的办法是声明一个几个对象，包括工作表和范围，以便您可以执行循环，而不选择或激活代码中的任何内容。因为任何Excel VBA开发者都值得他的盐会告诉你，应该始终避免选择和激活。

您可以使用以下模板：

  Sub LoopUntilLastRow（）
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim cell As Range 
 Dim lastRow As Long 
 Dim myColumn As String 
 
设置ws = ThisWorkbook.Sheets（Sheet1）
 myColumn =A
 lastRow = ws。范围（myColumn& ws.Rows.Count）.End（xlUp）.Row 
如果lastRow = 1然后
 MsgBoxColumn& UCase（myColumn）&  是空的。 
退出子
结束如果
 
设置rng = ws.Range（myColumn&1：& myColumn& lastRow）
对于每个单元格在rng 
'做某事
 cell.Interior.Color = vbYellow 
下一个单元格
 End Sub 
  

I was lucky enough to get a response from someone on a previous question I have asked. See here.

Only thing I am struggling with is creating a loop that will end the macro upon reaching a blank cell in Column B. I have been trying to read other code but nothing was really clicking for me (if, than loops, End With Loops, etc). This is the last step to my macro and I would love any resources on how to create loops and/or a solution to this issue for me. Thanks as always. The people on this site are beyond helpful!

Sub Redirect()
Dim IE As Object
Dim doc As Object

Set IE = CreateObject("InternetExplorer.Application")

With IE
    .Visible = True
    .Navigate "https://...."

    Do Until .ReadyState = 4: DoEvents: Loop

    With .Document.forms("digiSHOP")
        .elements("OldUrl").Value = Range("A2")
        .elements("NewUrl").Value = Range("B2")
        .submit
    End With

    Do Until .ReadyState = 4: DoEvents: Loop
    Do While .Busy: DoEvents: Loop

End With
End Sub

解决方案

While you might actually want to loop until you get to a blank row, most people find it more useful to loop until the last populated row in a particular column, which works even if you have a few (or many) blanks in the column.

Note that you may not even need a loop. Using .autofilter or .find are much more efficient. (comparable to using a where clause in a SQL query instead of a cursor)

Having said all that, if you do need the loop, the best approach is to declare a few objects including the worksheet and range so that you can execute the loop without selecting or activating anything in your code. As any Excel VBA developer worth his or her salt will tell you, selecting and activating should always be avoided.

You can use the following as a template:

Sub LoopUntilLastRow()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim myColumn As String

    Set ws = ThisWorkbook.Sheets("Sheet1")
    myColumn = "A"
    lastRow = ws.Range(myColumn & ws.Rows.Count).End(xlUp).Row
    If lastRow = 1 Then
        MsgBox "Column " & UCase(myColumn) & " is empty."
        Exit Sub
    End If

    Set rng = ws.Range(myColumn & "1:" & myColumn & lastRow)
    For Each cell In rng
        ' do something
        cell.Interior.Color = vbYellow
    Next cell
End Sub

这篇关于在Excel的VBA宏中创建循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！