设置未知的数组边界和循环 [英] Setting Unknown Array Boundaries and Loop

问题描述

我在数组边界上遇到麻烦。我有3个工作表。我将前两个转换为两个数组（array1& array2），然后在它们之间进行计算以创建第三个数组。问题是我不确定第三个数组的边界是什么，因为它总是根据输入而变化。

我一直在使用 Dim array3（5000，5 ），其中包含5000行（第1维）的虚拟对象，因为我认为不会超过该数量。有没有一种方法可以创建无边界的数组，然后向其中添加信息，然后使维度变暗？

I am having trouble with the boundaries of an array. I have 3 worksheets. The first 2 have i convert into two arrays (array1 & array2), and then do calculations between them to create the third. The issue is I am not sure what the boundaries are of the third array, since it will always change depending on the inputs.

I have been just using Dim array3 (5000, 5) with a dummy of 5000 rows (1st dimension) since I don't think there will be more than that. Is there a way to create the array with out boundaries and then add info to it, then dim the dimensions?

我创建的宏也利用了这段代码- -

Also the Macro I created utilizes this piece of code ---

     Z = 1
     For x = 1 To UBound(array1, 1)

     For y = 1 To UBound(array2, 1)

      If array1(x, 4) = 0 Then
        GoTo Line1
        End If

        If array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3)Then

            If array1(x, 4) > array2(y, 5) Then

                array3(z, 1) = array1(x, 3)

            ElseIf array1(x, 4) = array2(y, 5) Or array1(x, 4) < array2(y, 5) Then

                array3(z, 1) = array1(x, 3)

            End If

        z = z + 1

     End If

    Next y

Line1:
Next x

它需要一块array1并循环通过array2并在array3中创建结果

基本上在array1（x，4）= 0时，我需要它前进到下一个X。如果没有GoTO Line1，我不知道如何循环播放。如果我将其向下移动，则它将继续循环通过arry2（y），而不是继续前进至下一个X。如果我将其向上移动，则y将重置并再次通过For y循环

It takes a piece of array1 and loops it through array2 and creates a result in array3

Basically when array1(x, 4) = 0, I need it to move on to the next X. I can't figure out how to loop this without the GoTO Line1. If i move it down, then it will continue to loop through arry2 (y), instead of moving on to next X. If i move it above, then y resets and it runs through the For y loop again

推荐答案

您可以这样做：

• 将array3调暗到理论上最大的行数


• 来回移动array3并将 redim 的行数转换为实际填充的行数

• dimming array3 to the theoretically maximum rows number
• transposing array3 back and forth and redim its rows number to the actually filled ones

像这样的（注释）代码：

like per this (commented) code:

    Dim x As Long, y As Long, z As Long
    Dim array1 As Variant, array2 As Variant

    array1 = ... ' your way of filling array1
    array2 = ... ' your way of filling array2

    ReDim array3(LBound(array1, 1) To UBound(array1, 1) * UBound(array2, 1), LBound(array1, 1) To LBound(array1, 2) ' dim Array 3 to the theoretically maximum number of rows and to the wanted columns number (here, the same as array1 columns

    z = LBound(array3, 1) - 1 'start from array3 rows number lower bound minus one to update at every matching criteria
    For x = LBound(array1, 1) To UBound(array1, 1)

        For y = LBound(array2, 1) To UBound(array2, 1)
            If array1(x, 4) <> 0 Then
                If array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3) Then
                    z = z + 1
                    If array1(x, 4) > array2(y, 5) Then
                        array3(z, 1) = array1(x, 3)
                    Else
                        array3(z, 1) = array2(x, 3) ' see my guess here instead of your original code
                    End If
                End If
            End If
        Next
    Next

    If z >= 0 Then
        array3 = Application.Transpose(array3) 'transpose array3
        ReDim Preserve array3(LBound(array1, 1) To LBound(array1, 1) + 1, LBound(array1, 1) To z) 'redim its columns to their actually filled number, while preserving values
        array3 = Application.Transpose(array3) 'trasnpose back your array3
    End If

这篇关于设置未知的数组边界和循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！