Excel VBA-从数组到数组匹配值 [英] Excel VBA - matching value from array to array

问题描述

我正在比较2个数组.1是动态数组，1是静态数组，都是大小不同的一维数组.条件是，如果动态数组中的值与静态数组中的值匹配，则它将采用单元格值，并且还会发生其他操作.

I'm comparing 2 array. 1 is dynamic array and 1 is static array, both are 1-Dimension array with different size. The condition is that if the value in the dynamic array matches to the value in the static array, then it would take cell values and some other action would happens.

案例:

dynArr = (123,123,45,23,56,90,34,Nil) ' size based on row counts
staArr = (90,60,30,0)

因此，如果 dynArr 中的任何值与 staArr 相匹配，就会发生某些事情.

So if any value in dynArr matches to staArr, some thing happens.

现在，我认为我的情况出了点问题，因为它从excel行中获取了所有值.

Right now, I think that something is wrong with my condition as it gets all the values from the excel rows.

我尝试使用过 application.match()，但其说明来自

I tried used application.match() but with description from msdn, it does not seem to work and in terms of speed wise, it much more slower from some other posts.

如何更改条件以在阵列之间匹配?还是有任何解决方法?

How should I change my condition to match between arrays? or is there any workaround?

Sub getValue()

'define dynamic array
Dim sn, sb, interval, daysleft As Variant
'define static array
interval = Array(90, 60, 30, 14, 7, 0, -2)

Dim cnt As Long
Dim i, x, y As Long

ReDim sn(1 To 1)
ReDim sb(1 To 1)
ReDim daysleft(1 To 1)

cnt = 0

'Loop through all the row
For i = 1 To Cells(Rows.Count, "L").End(xlUp).Row
    'redim daysleft based on the rows
    ReDim Preserve daysleft(1 To i)
    daysleft(i) = Cells(i, "L").Value

    For x = LBound(daysleft) To UBound(daysleft)
     For y = LBound(interval) To UBound(interval)

        If daysleft(x) = interval(y) Then 'Loops everything

            'assign cell value to array
            cnt = cnt + 1
            ReDim Preserve sn(1 To cnt)
            ReDim Preserve sb(1 To cnt)

            sn(cnt) = Cells(i, "A").Value
            sb(cnt) = Cells(i, "F").Value

         End If
        Next y
    Next x
Next i

For i = 1 To (cnt - 1)
    Debug.Print "This is sn: "; sn(i) 'gets all the value instead of the matching case
  'Call test(sn(i), sb(i))
Next

End Sub

推荐答案

确定.我想这就是你想要的.我注释掉了For x循环.您拥有的i循环将剩余所有天数都加载起来.出现x循环会导致您在间隔中多次匹配相同的值.

OK. I think this is what you want. I commented out the For x loop. The i loop that you had loads up all of the daysleft. Having the x loop was causing you the match the same value in the interval more than once.

Sub getValue()

    'define dynamic array
    Dim sn, sb, interval, daysleft As Variant
    'define static array
    interval = Array(90, 60, 30, 14, 7, 0, -2)

    Dim cnt As Long
    Dim i, x, y As Long

    ReDim sn(1 To 1)
    ReDim sb(1 To 1)
    ReDim daysleft(1 To 1)

    cnt = 0

    ' Loop through all the row
    For i = 1 To Cells(Rows.Count, "L").End(xlUp).row
        'redim daysleft based on the rows
        ReDim Preserve daysleft(1 To i)
        daysleft(i) = Cells(i, "L").Value

        'For x = LBound(daysleft) To UBound(daysleft)
            For y = LBound(interval) To UBound(interval)

               If daysleft(i) = interval(y) Then 'Loops everything

               'assign cell value to array
               cnt = cnt + 1
               ReDim Preserve sn(1 To cnt)
               ReDim Preserve sb(1 To cnt)

               sn(cnt) = Cells(i, "A").Value
               sb(cnt) = Cells(i, "F").Value

            End If
            Next y
         'Next x
     Next i

     For i = 1 To (cnt)
          Debug.Print "This is sn: "; sn(i) 'gets all the value instead of the matching case
         'Call test(sn(i), sb(i))
     Next

End Sub

这篇关于Excel VBA-从数组到数组匹配值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！