在VBA中循环?我想使用一个循环来选择并复制到最后一个单元格 [英] Loops in VBA? I want to use a loop to select and copy till last cell

问题描述

我试图选择行K中的每个连续单元格(从范围K1开始)，然后对每个向下的单元格复制值并将其粘贴到单元格M10中.但是，按照当前写入宏的方式，宏选择了范围K中最后一个单元正下方的单元，因此将空白复制到M10中.相反，我希望循环一次处理一个单元.我想一次选择一个单元格并将其复制，即循环将选择K1并将其复制到M10，然后选择K2并将其复制到M10，依此类推，然后使循环在范围K的最后一个单元格之后停止.

I am trying to select each consecutive cell in Row K (starting from Range K1), and for each cell going down, copy the value and paste it into Cell M10. However, the way the macro is written currently, the macro is selecting the cell right below the last cell in Range K, and is thus copying a blank into M10. Instead, I want the loop to work down one cell at a time. I want to select one cell at a time and copy it, i.e. the Loop will select K1 and copy it to M10, then select K2 and copy it to M10, etc, and then have the loop stop after the last cell of Range K.

有人可以帮我吗?

Sub test() 

lastcell = Range("K" & Cells.Rows.Count).End(xlUp) 

Range("K2").Select 
Do 
ActiveCell.Offset(1, 0).Select 
Selection.Copy 
Range("M10").Select 
Selection.PasteSpecial
Application.Run ("Second Macro") 
Loop Until IsEmpty(ActiveCell.Value) 

End Sub

推荐答案

您可以使用下面的小脚本遍历K列:

You can loop through column K using the small script below:

Option Explicit
Sub LoopThroughColumnK()

Dim LastRowInColK As Long, Counter As Long
Dim SourceCell As Range, DestCell As Range
Dim MySheet As Worksheet

'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet1")
With MySheet
    LastRowInColK = .Range("K" & .Rows.Count).End(xlUp).Row
    Set DestCell = .Range("M10")
End With

'loop through column K, copying from cells(counter, 11) to M10
With MySheet
    For Counter = 1 To LastRowInColK
        Set SourceCell = .Range("K" & Counter)
        SourceCell.Copy Destination:=DestCell
        'call MyMacro below
        '... doing cool MyMacro stuff
    Next Counter
End With

End Sub

要总结正在发生的事情，我们:

To summarize what's happening, we:

1. 分配工作表变量，以确保我们在正确的工作表上工作
2. 为最后一行和单元格M10分配易于阅读和参考的变量
3. 浏览相关范围，从Kn复制并粘贴到M10

该技术还避免使用.Select，它是运行时错误的常见来源.这是一篇惊人的文章，概述了许多不使用.Select和.Activate的方法:

This technique also avoids using .Select, a common source of run-time errors. Here's an AMAZING post outlining lots of ways to NOT use .Select and .Activate: How to avoid using Select in Excel VBA macros

编辑:我在上面的评论中描述的重构可以轻松完成.让我们将整个问题分成两个小块:

The refactoring I described in my comment above could be implemented without too much struggle. Let's break the whole problem into two bite-size chunks:

1. 获取K列中所有已占用的单元格并将其另存为Range
2. 针对在上面的步骤#1中保存的Range中的每个Cell，运行从单元格M10锁定的辅助宏.我们现在将第二个宏称为MyOtherMacro

1. Get all the occupied cells in column K and save them as a Range
2. Running your secondary macro, which was keyed off cell M10, for each Cell in the Range we saved in step #1 above. We'll call the secondary macro MyOtherMacro for now

让我们开始吧.星期天，大家！下面的代码非常注释，可以解释每个功能和步骤中发生的事情:

Let's get after it. Sunday Funday y'all! The code below is heavily-commented to explain what's happening in each function and step:

Option Explicit
Sub DoWork()

Dim MySheet As Worksheet
Dim ColKRange As Range

'set the worksheet we want to work on, in this case "Sheet1"
Set MySheet = ThisWorkbook.Worksheets("Sheet1")

'get the range of occupied cells in col K
Set ColKRange = OccupiedRangeInColK(MySheet)

'kick off the other macro using the range we got in the step above
Call MyOtherMacro(ColKRange)

End Sub

DoWork(上面)是控制器"类型的脚本.它所做的只是启动下面我们编写的其他两个功能OccupiedRangeInColK，然后又是MyOtherMacro.

DoWork (above) is a "controller"-type script. All it does is kick off the other two functions we have written below, OccupiedRangeInColK and then, one step later, MyOtherMacro.

'this function returns a range object representing all
'the occupied cells in column K, starting at row 1 and ending
'at the last occupied row (in column K)
Public Function OccupiedRangeInColK(TargetSheet As Worksheet) As Range
    Dim LastRow As Long

    'check for unassigned worksheet object, return nothing if that's the case
    If TargetSheet Is Nothing Then
        Set OccupiedRangeInColK = Nothing
    End If

    With TargetSheet
        LastRow = .Range("K" & .Rows.Count).End(xlUp).Row
        Set OccupiedRangeInColK = .Range(.Cells(1, 11), .Cells(LastRow, 11))
    End With
End Function

很酷-描述性名称在编写脚本时是一件很棒的事情. OccupiedRangeInColK(上方)取一个Worksheet，然后从列K中返回占用的Range.

Cool -- descriptive names are a great thing when it comes to scripting. OccupiedRangeInColK (above) takes a Worksheet, then returns the occupied Range from column K.

'this function is a shell to be populated by @polymorphicicebeam
Public Function MyOtherMacro(TargetRange As Range)
    Dim Cell As Range

    'check for an empty range, exit the function if empty
    If TargetRange Is Nothing Then Exit Function

    'loop through all the cells in the passed-in range
    For Each Cell In TargetRange
        'Do cool stuff in here. For demo purposes, we'll just
        'print the address of the cell to the screen
        MsgBox (Cell.Address)
    Next Cell
End Function

最后，MyOtherMacro(在上方)是您添加自己的魔法的地方.我为您构建了一个"shell"函数，该函数仅使用MsgBox打印相关单元的地址.您可以在For Each Cell In TargetRange循环内的指示处添加自己的逻辑.哇！

Finally, MyOtherMacro (above) is where you get to add your own magic. I built a "shell" function for you, which simply prints the address of the cell in question with a MsgBox. You can add your own logic where indicated inside the For Each Cell In TargetRange loop. Woo!

这篇关于在VBA中循环?我想使用一个循环来选择并复制到最后一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！