Combining multiple macros

Question

I am trying to make a Macros that will count the number of cities/possible cities listed in a column but since I have a limited of character to write in the 1st Array that I have created.(It was working that way). I then added Dim Cities2 () with the 2nd Array but getting the error "Type mismatch error". By the way, I still need to add about 200 more cities in the array list but did not add them yet.

Public Sub CountLocation1()

Dim wb As Workbook
Dim ws As Worksheet
Dim lastCell As String
Dim countRange As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet 'Change as appropriate

Set countRange = ws.Range(Cells(2, "V"), Cells(ws.Range("V2").End(xlDown).Row, "V"))

Debug.Print countRange.Address

Dim Cities()
Cities = Array("Auckland", "Brisbane", "Melbourne", "Seoul", "Tokyo", "Sydney", "Bratislava", "Bangalore", "Chennai", "Gurgaon", "Hyderabad", "Kolkata", "New Delhi", "Noida", "Mumbai", "London", "Munich", "Unterfohring", "Aachen", "Abidjan", "Abington", "Alpharetta", "Amstelveen", "Amsterdam", "Anaheim", "Aquascalientes", "Arlon", "Ashland", "Atlanta", "Aurora", "Austin", "Barcelona", "Basel", "Batavia", "Bay Village", "Belton", "Berkshire", "Berlin", "Birmingham", "Bogota", "Boise", "Boston", "Bramley", "Brandon", "Brecksville", "Brentwood", "Bridgetown", "Brussels", "Budapest", "Buffalo Grove", "Bury", "Cairo", "Callahan", "Calumet City", "Cape Town", "Capitola", "Cardiff", "Carmel", "Centennial", "Chanhassen", "Charlotte", "Cheltenham", "Cincinnati", "Clearwater", "Clemson", "Cleveland", "Cohoes", "Columbia", "Columbus", "Conifer", "Cookeville", "Copenhagen", "Coral Gables", "Croydon", "Culver City", "Cumming", "Cutchogue", "Dallas", "Dallas Park", "Darmstadt", "Double Oak", "Dublin")

Dim Cities2()
Cities2 = Array("Eagle Rock", "East Hartford", "Elk Grove", "Encino", "Enfield", "Erfurt", "Eschborn", "Euless", "Fairfield", "Fenton", "Folkestone", "Folsom", "Frankfurt", "Franklin", "Frisco", "Garden City", "Geneva", "Germantown", "Glendale", "Glenview", "Gloucester", "Greensboro", "Greenwood", "Greenwood Village", "Grove", "Hamburg", "Hamilton", "Harrisonville", "Hartford", "Hatfield", "Hiawatha", "Hitchin", "Hofstetten", "Homewood", "Hope", "Houston", "Hudson", "Illinois", "Indianapolis", "Itasca", "Jackson", "Jacksonville", "Jaipur", "Johannesburg", "Jordbro", "Katy", "Kirkwood", "Ladera Ranch", "Lake Forest", "Lakewood", "Lancaster", "Largo", "Lawrenceville", "Leawood", "Lexington", "Liberty", "Lincoln", "Lockport", "Lombard", "Luxembourg", "Lörrach", "Madrid", "Manchester", "Maple Glen", "Martins Ferry", "Marupe", "Masontown", "Matthews", "McKinney", "Mechanicsville", "Middletown", "Milan")

Dim city As Long
Dim counter As Long
Dim startRange As Range
Set startRange = ws.Cells(ws.Range("V2").End(xlDown).Row, "V").Offset(2, 0)

counter = 2

For city = LBound(Cities, Cities2) To UBound(Cities, Cities2)
  If Application.WorksheetFunction.CountIf(countRange, Cities(city)) > 0 Then
    startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities, Cities2(city))
    startRange.Offset(counter, 1) = Cities & Cities2(city)
     counter = counter + 1

  End If

Next city


End Sub

Answer 1

You can try a different approach.

Create a Dictionary object and add the cities as Keys. The good thing about dictionaries is that you can check if a Key (city) exists.

If the city exists, increment the value (counter). If not, add it with a value of 1.

The example below adds the list located in column A. Modify it to fit your needs.

Sub GetCityCount()

    Dim ws As Worksheet
    Dim objDict As Object

    Set ws = ThisWorkbook.ActiveSheet
    Set objDict = CreateObject("Scripting.Dictionary")

    Dim lngCount As Long
        lngCount = ws.Cells(Rows.Count, "A").End(xlUp).Row 'Column A

    Dim idx As Long
    For idx = 1 To lngCount
        If Not objDict.Exists(ws.Cells(idx, 1).Value) Then
            'Add to dictionary with a count of 1
            objDict.Item(ws.Cells(idx, 1).Value) = 1
        Else
            'Increment counter
            objDict.Item(ws.Cells(idx, 1).Value) = objDict.Item(ws.Cells(idx, 1).Value) + 1
        End If
    Next

    'Test
    Dim k As Variant
    For Each k In objDict.Keys
        Debug.Print "Key: " & k & ", Count: " & objDict(k)
    Next k
End Sub

Sample Data:

A

A

B

B

C

C

C

D

E

F

F

G

H

R

T

T

T

Output:

'Key: A, Count: 2
'Key: B, Count: 2
'Key: C, Count: 3
'Key: D, Count: 1
'Key: E, Count: 1
'Key: F, Count: 2
'Key: G, Count: 1
'Key: H, Count: 1
'Key: R, Count: 1
'Key: T, Count: 3

Answer 2

Using single letter or letter number combinations for your counters will add to the readability of your code. Whenever you see i, j, x, y, i1, i2 ...etc. you should know that it is a counter.

The first parameter for LBound and UBound is an Array and the second parameter is the Dimension that you are targeting.

Here are typical For loops used to iterate over arrays:

For x = LBound(Cities) To UBound(Cities)
    Debug.Print Cities(x) '1D Array
Next

For x = LBound(Cities) To UBound(Cities)
    Debug.Print Cities(x, 2) 'The 2 column of a 2D Base 1 array
Next

For x = LBound(Cities, 2) To UBound(Cities, 2)
   '2nd column of a 2D Base 1 array
Next

It is common practice to use nouns as the variable used in a For Each loop. Here is a typical pattern:

Note: the variable must be of either Variant or Object type depending on the situation. Variant type always works. You can use this loop on either a 1D or multidimensional array. This loop iterates over each element of the array. After it completely iterates over the elements of the first column (dimension) it start at the beginning of the next column (dimension).

Dim city As Variant

For Each city In Cities
    Debug.Print city
Next