Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using Array for Match/Count Loop

0

Hello - So, I am about 4 months into learning VBA and am struggling in doing a loop that returns a count.  The attached file provides two tabs, "Sampled Data" and, "Code."  Here is the code. Can someone help me?

I hope to hear from you soon.

Thanks, Cora

Sub CountEidDaMem()

Dim vs As Worksheet
Set vs = ThisWorkbook.Sheets("SAMPLED DATA")

Dim erow As Long
erow = vs.CellS(Rows.Count, 1).End(xlUp).Row

Dim i As Long
i = 8

'Column A Employee Id
Dim Eid As Long
Eid = vs.CellS(i, 2).Value
Dim Eid2 As Long
Eid2 = vs.CellS(i + 1, 2).Value

'Column D Expense Item Date
Dim ExpD As Long
ExpD = vs.CellS(i, 5).Value
Dim ExpD2 As Long
ExpD2 = vs.CellS(i + 1, 5).Value

'Column L Counts
Dim CMem As Long
CMem = vs.CellS(i, 12).Value
Dim CMem2 As Long
CMem2 = vs.CellS(i + 1, 12).Value
Dim CMem3 As Long
CMem3 = vs.CellS(i + 2, 12).Value


Dim MyArray() As Variant
Dim Dimension1 As Long
Dimension1 = Range("B8", Range("B7").End(xlDown)).CellS.Count - 1
Dim Dimension2 As Long
Dimension2 = Range("B7", Range("B7").End(xlToRight)).CellS.Count - 7


ReDim MyArray(0 To Dimension1, 0 To Dimension2)

'Loop
       For Dimension1 = LBound(MyArray, 1) To UBound(MyArray, 1)
        For Dimension2 = LBound(MyArray, 2) To UBound(MyArray, 2)
            MyArray(Dimension1, Dimension2) = Range("B8").Offset(Dimension1, Dimension2).Value
                
        Next Dimension2
    Next Dimension1
   
    Dim Arr As Variant
    Dim x As Long
    Dim y As Date
   

        For Each Arr In MyArray

           x = Application.WorksheetFunction.Match(MyArray, Eid, 0)
           y = Application.WorksheetFunction.Match(MyArray, ExpD, 0)
           Arr.CMem.Value = Data.Count("x:y", Range("D4").Value)
           Arr.CMem2.Value = Data.Count("x:y", Range("D3").Value)
           Arr.CMem3.Value = Application.WorksheetFunction.Count("x:y", Range("D2").Value)
                        
        CountEid = CountEid + 1
     Next Arr
End Sub


Answer
Discuss

Answers

0

Hello Cora,

Welcome back. I have reviewed your code, entered it into your workbook and added a lot of comments. The result is attached.

You are doing a fantastic job with arrays but should learn about cells and ranges. Arrays and Ranges work real well together.

Excel defines a range as starting from the first cell and ending at the last, like "A1:B10" in worksheet functions. In VBA syntax that could be Range("A1:B10"). The smallest range has a single cell. Therefore Range("A1") is also a correct range address. But then Range("B10") must also be correct (it is!). And if a range is defined by its first and last cells than Range(Range("A1"), Range("B10")) must also be correct (it is!).

However, identifying cells with string expressions isn't true VBA. I call it baby-VBA, in fact allowing worksheet syntax into VBA. True VBA identifies a cell by row and column, both of them numbers. B10 = Cells(10, 2). "Cells" refers to the collection of all cells in a worksheet. The syntax specifies, "from all cells, I want the one where the row = 10 and the column = 2". That is Cells(10, 2). A variation of this is Cells(10, "B") - call it half-baby. It's slower than Cells(10,2) because VBA must translate the "B" to 2. There is extra work but much less than Range("B10").

Now you can specify a range like Range(Cells(1,1), Cells(10,2)) which is the same as Range(Cells(1,"A"), Cells(10,"B")) in half-baby syntax.

Cells(Rows.Count, 2) or Cells(Rows.Count, "B") also specifies a cell. Rows.Count is the number 1048576. Cells(Rows.Count, 2) = B1048576. Cells(Rows.Count, 2).End(xlUp) specifies a cell above the last cell in the column. Logically, you can use this cell to specify a range, like Range(Cells(1,1), Cells(Rows.Count, 2).End(xlUp)). Observe the comma separating first and last cell.

All of the above is on the ActiveSheet, by default, because no sheet is specified. In fact, most of the time you want the "Cells" from a particular sheet as well as the Rows.Count, and the range should be on that same sheet, too. For this you would use a With statement as shown below. There are 4 periods preceding properties connecting to Vs. Spot them.

Set Vs = Worksheets("Sheet3")

With Vs
    Set Rng = .Range(.Cells(1,1), .Cells(.Rows.Count, 2).End(xlUp))
End With

Commands like Activate or Select are parts of Excel's user interface. VBA doesn't need them. In the above code neither the worksheet Vs, nor the range Rng, are "activated". But you did use Vs to define Rng and you can now use Rng to assign its value to an array, like MyArr = Rng.Value. You can then manipulate MyArr, like MyArr(1,1) = "This is cell A1" and return the changed array to the sheet, like Rng.Value = MyArr. Observe that VBA remembers the sheet on which Rng was specified. It's part of the Range objects DNA. Try ? Rng.Worksheet.Name in the Immediate Window.

As to the counting, I'm afraid your question is too little defined for an answer. In this session let's focus on getting your range properly read into an array. With that done please ask another question about what you want to do with the array.

Discuss

Discussion

Hi and thank you. I appreciate your comments. I get confused when appropriate to use Ranges versus Cells.  I have the following questions so I can better understand. 
Dimension1 = Range("B8", Range("B7").End(xlDown)).Cells.Count - 1
    ' Therefore: correct syntax is more like shown below
Dimension1 = Range(Range("B8"), Range("B7").End(xlDown)).Cells.Count – 1     '            but is it what you mean to say?   ‘I am saying that I don’t want to include my header.  Is this what your syntax means? Could I simply do Range(“B8”, Range(“B8”).End(xlDown)).Cell.Count?      Dim vs As Worksheet
    Set vs = ThisWorkbook.Sheets("SAMPLED DATA")  
‘Why doesn’t this action automatically activate the sheet since I am creating the code for it?Can I do vs.Activate in the declaration?     For Dimension1 = LBound(MyArray, 1) To UBound(MyArray, 1)
        For Dimension2 = LBound(MyArray, 2) To UBound(MyArray, 2)
' No worksheet specified: B7 is on the ActiveSheet! – ‘Is the way I have it listed above okay?      
CoraG (rep: 6) Feb 11, '19 at 2:24 pm
Cora, please help me keeping this forum useful and interesting for everyone by not expanding upon your questions in the Discussion section. Questions are free. Ask another one.
Variatus (rep: 4889) Feb 11, '19 at 8:39 pm
YOU ROCK!! No problem and thank you for explaining things to me.  This has been tremendously helpful.  Okay here is a question.
1.  You mentioned by default VBA assigns columns to the 1st dimension and reows to the 2nd dimension.  So when I ReDim MyArray am I switching it to
'ReDim MyArray(1 to Deimsion2, 1 to Dimension1)?
CoraG (rep: 6) Feb 13, '19 at 11:57 am
To be honest, Cora, I do it wrong 80% of the time which defies the odds because it's a binary choice and should be 50:50. It's easy to test. MyArr = Range("A1:C4") will mean that MyArr(1,1) has the value of A1 and B1 will either be MyArr(1,2) or MyArr(2,1). If I told you the correct thing than it should be MyArr(2,1) because column B = column 2 and (2,1) is the second column in row 1. If I didn't, please forgive me and turn the numbers around.
Variatus (rep: 4889) Feb 13, '19 at 8:44 pm
Hi there... This piece of the code seem to do the trick perfectly.  Would there be a need to redim?  Try it and let me know your thoughts. Thank you.

Sub CounteidDaMem()

    Dim Vs As Worksheet
    Set Vs = Sheets("SAMPLED DATA")
    Vs.Activate
    Dim Rng As Range
    Dim Eclm As Long
    Dim erow As Long
    Dim mYaRRay As Variant
   
    With Vs
        erow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Eclm = .Cells(7, .Columns.Count).End(xlToLeft).Column
        Set Rng = .Range(.Cells(8, "B"), .Cells(erow, Eclm))
    End With
   
        Debug.Print Rng.Address                 ' this range is on worksheet vs
        mYaRRay = Rng.Value
        Debug.Print mYaRRay(1, 1), mYaRRay(2, 1), mYaRRay(2, 2), mYaRRay(2, 3), mYaRRay(2, 4)
       
End Sub
CoraG (rep: 6) Feb 20, '19 at 11:19 am
MyArr is redimmed to the size of Rng by the action of assigning Rng's value to it.
Variatus (rep: 4889) Feb 22, '19 at 3:53 am
Sweet, thank you
CoraG (rep: 6) Feb 22, '19 at 1:41 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login