Check if value exist into two-dimensional tab (array, vb)

Question

I creating tab(x,y) with values:

Dim CostCatTab() As Variant
Dim tabSize As Long

For m = 3 To CCRLastColumn

    CostCategory = wsSum.Cells(CostCagRow, m).Value
    Value = wsSum.Cells(CostCagRow, m).Value

    tabSize = tabSize + 1
    ReDim Preserve CostCatTab(1 To 2, 1 To tabSize)
    CostCatTab(1, tabSize) = CostCategory
    CostCatTab(2, tabSize) = m

Next

Into second loop I want to check if current item (currentCT) is into CostCatTab.CostCategory.

If yes, then I want to display appropriate m value for CostCategory.

For h = 10 To x_rows
    currentCT = wsCal.Range("M" & h).Value

Next

The final `tabSize` is `CCRLastColum - 2`, it would be quicker to use only one Redim before the start of the loop without preserve. And `tabSize` is always `m - 2`, you could use only one variable here. — Vincent G, Jan 13 '17 at 08:14
Shouldn't the line `CostCatTab(2, tabSize) = m` be `CostCatTab(2, tabSize) = Value`? — Vincent G, Jan 13 '17 at 08:17
And `CostCagRow` doesn't change in the loop, so the `CostCategory` and `Value`are the same and won't change. — Vincent G, Jan 13 '17 at 08:20
I would suggest using a dictionary for this. If you want to look through a list of unique keys and return a value, a dictionary is your best option, and you can use the In fuction, which is easier to deal with than an array. Introduction here if you're interested: http://www.cpearson.com/excel/CollectionsAndDictionaries.htm — Preston, Jan 13 '17 at 09:37

score 0 · Accepted Answer · answered Jan 13 '17 at 10:08

I did:

 For h = 10 To x_rows
        currentCT = wsCal.Range("M" & h).Value

    For b = LBound(CostCatTab, 2) To UBound(CostCatTab, 2)
        If CostCatTab(1, b) = currentCT Then
            wsCal.Range("N" & n).Value = wsCal.Range("K" & n).Value * wsSum.Cells(40, CostCatTab(2, b))
            Exit For
        End If
    Next b

 Next h

Check if value exist into two-dimensional tab (array, vb)

1 Answers1