通过在Excel VBA中的数组循环

问题描述

我试图让熟悉VBA数组。我想要复制，在另一片含有1并将其放置在列表的末尾，然后从原始片再删除线条。这里是code我放在一起。这是行不通的。

任何人能帮助我吗？

 子数组1（）
昏暗州（）为Variant
昏暗DIMENSION1只要
昏暗我只要
昏暗dvojPole（）为Variant工作表（live_position）。激活州=范围（A2，范围（A1）。完（xlDown））
DIMENSION1 = UBound函数（州，1）
使用ReDim dvojPole（1要DIMENSION1，1到2）对于i = 1到DIMENSION1
设置DEST =工作表（封闭）。范围（A1）。完（xlDown）.Offset（1,0）
    如果dvojPole（I，1）= 1，则
        dvojPole（I，1）.EntireRow.Copy目的地：= DEST
        dvojPole（I，1）.EntireRow.Delete
    万一接下来，我
结束小组
 

解决方案

考虑下面的例子。你没有正确定义数组并在其值不重复。这里的关键是引用，以便在整个范围内进行迭代单元格的值位置和实际单元格值。

 对错误转到ErrHandle：
    昏暗州（）为Variant
    昏暗xlcell作为范围，cleanupRng作为范围
    昏暗我作为整数，J为整数，K为整数，L为整数，计数器作为整数    '定义数组的尺寸
    使用ReDim州（0到范围（A2，范围（A1）。完（xlDown））。计数）    INSERT VALUES（单元格位置）数组
    I = 0
    对于每个xlcell在范围（A2，范围（A1）。完（xlDown））
        州（I）= xlcell.Address（假，假，xlA1）
        I = I + 1
    接下来xlcell    J =工作表（关闭），范围（A1）。完（xlDown）.Row + 1    ITERATE ACROSS ARRAY
    对于k = LBOUND（州）要UBound函数（州） -  1
        如果范围（州（K））= 1，则
            。范围（州（k））的复制目标：=工作表（关闭）的范围（A＆放大器; J）
            范围（州（K））。EntireRow.ClearContents
        万一
    当J = J + 1个
    下面k    DELETING清空了ROWS
    设置cleanupRng =范围（A1：A＆放大器; ActiveSheet.UsedRange.Rows.Count）    L = 1
    对于计数器= 1到ActiveSheet.UsedRange.Rows.Count
        如果莱恩（cleanupRng.Cells（1））= 0则
            cleanupRng.Cells（升）.EntireRow.Delete
        其他
            L = L + 1
        万一
    接下来计数器
    退出小组ErrHandle：
    MSGBOX的Err.Number＆安培;  - 与＆amp; Err.Description它将
    退出小组
 

I am trying to get familiar with arrays in VBA. I Want to copy lines that contain "1" in another sheet and place them at the end of the list and then delete then from the original sheet. Here is the code I put together. It does not work.

Can anybody help me please?

Sub array1()
Dim Oblast() As Variant
Dim dimension1 As Long
Dim i As Long
Dim dvojPole() As Variant

Worksheets("live_position").Activate

Oblast = Range("A2", Range("A1").End(xlDown))
dimension1 = UBound(Oblast, 1)
ReDim dvojPole(1 To dimension1, 1 To 2)

For i = 1 To dimension1
Set dest = Worksheets("closed").Range("A1").End(xlDown).Offset(1, 0)
    If dvojPole(i, 1) = 1 Then
        dvojPole(i, 1).EntireRow.Copy Destination:=dest
        dvojPole(i, 1).EntireRow.Delete
    End If

Next i
End Sub

解决方案

Consider the below example. You did not properly define your array and did not iterate across its values. The key here is to reference the cell value location and actual cell value in order to iterate across the range.

On Error GoTo ErrHandle:
    Dim Oblast() As Variant
    Dim xlcell As Range, cleanupRng As Range
    Dim i As Integer, j As Integer, k As Integer, l As Integer, counter As Integer

    ' DEFINE DIMENSION OF ARRAY
    ReDim Oblast(0 To Range("A2", Range("A1").End(xlDown)).Count)

    ' INSERT VALUES (CELL LOCATION) IN ARRAY
    i = 0
    For Each xlcell In Range("A2", Range("A1").End(xlDown))
        Oblast(i) = xlcell.Address(False, False, xlA1)
        i = i + 1
    Next xlcell

    j = Worksheets("closed").Range("A1").End(xlDown).Row + 1

    ' ITERATE ACROSS ARRAY
    For k = LBound(Oblast) To UBound(Oblast) - 1
        If Range(Oblast(k)) = 1 Then
            Range(Oblast(k)).Copy Destination:=Worksheets("closed").Range("A" & j)
            Range(Oblast(k)).EntireRow.ClearContents
        End If
    j = j + 1
    Next k

    ' DELETING CLEARED ROWS
    Set cleanupRng = Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)

    l = 1
    For counter = 1 To ActiveSheet.UsedRange.Rows.Count
        If Len(cleanupRng.Cells(l)) = 0 Then
            cleanupRng.Cells(l).EntireRow.Delete
        Else
            l = l + 1
        End If
    Next counter
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description
    Exit Sub

这篇关于通过在Excel VBA中的数组循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！