CODEWITHSUNDEEP
vbaexcel
Jonathan
Jonathan

Reputation: 172

SUM of offset cells from an offset results

I need to sum the results of the one highlighted in red which I got from the startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities(city))

The results from the code does not have a fixed location as the listed cities in Column C is different everyday and in every sheet.

The 1st Results of total is from the code:

Public Sub count()

Dim lastCell As String
Range("C2").Select

Selection.End(xlDown).Select
lastCell = ActiveCell.Address

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=counta(C2:" + lastCell + ")"
End Sub

The 2nd results of total is from the code: citiesCount = countRange.Rows.Count . The problem with this code is that if a city is not listed in my array, it will still count it.

enter image description here

Here is the full code.

Public Sub B1_Manual_CountLocations()

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

count 'Call the function to count the total of cities 

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

Set countRange = ws.Range(Cells(2, "C"),Cells(ws.Range("C2").End(xlDown).Row, "C"))

Dim Cities()
Cities = Array("Armonk", "Bratislava", "Bangalore", "Hong Kong", "Mumbai", Zurich")
Dim city As Long
Dim counter As Long
Dim startRange As Range

Set startRange = ws.Cells(ws.Range("C2").End(xlDown).Row, "C").Offset(2, 0)

counter = 2

Dim citiesCount As Long
citiesCount = ((countRange.Rows.count) - 1) 'new line to hold total number of cities


For city = LBound(Cities) To UBound(Cities)

  If Application.WorksheetFunction.CountIf(countRange, Cities(city)) > 0 Then
startRange.Offset(counter, -1) = Application.WorksheetFunction.CountIf(countRange, Cities(city)) / citiesCount 'new line to calculate proportion of total
startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities(city))
startRange.Offset(counter, 1) = Cities(city)

counter = counter + 1 

  End If


Next city
startRange.Offset(counter + 1, 0) = "Total:" & citiesCount 'count the total number of city

End Sub

I already checked SUM of offset cells that contain a value but this is not clear to me.

Upvotes: 1

Views: 339

Answers (1)

QHarr
QHarr

Reputation: 84465

You would need to add perhaps a function to check if the values in the count range exist in your array, if it does then for each match add 1 to your total cities count.

Using a function by Jimmy Pena you would have something like the following:

Option Explicit

Public Sub B1_Manual_CountLocations()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim countRange As Range
    Dim cell As Range

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

    Set countRange = ws.Range(ws.Cells(2, "C"), ws.Cells(ws.Range("C2").End(xlDown).Row, "C")) 'explicit worksheet reference

    Dim Cities()
    Cities = Array("Armonk", "Bratislava", "Bangalore", "Hong Kong", "Mumbai", "Zurich")
    Dim city As Long
    Dim counter As Long
    Dim startRange As Range
    Dim citiesCount As Long

    citiesCount = 0

    For Each cell In countRange

        If IsInArray(cell.Value, Cities) Then
            citiesCount = citiesCount + 1
        End If

    Next cell

    Set startRange = ws.Cells(ws.Range("C2").End(xlDown).Row, "C").Offset(2, 0)
    counter = 2

    For city = LBound(Cities) To UBound(Cities)

        If Application.WorksheetFunction.CountIf(countRange, Cities(city)) > 0 Then

            startRange.Offset(counter, -1) = Application.WorksheetFunction.CountIf(countRange, Cities(city)) / citiesCount 'new line to calculate proportion of total
            startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities(city))
            startRange.Offset(counter, 1) = Cities(city)

            counter = counter + 1

        End If

    Next city

    startRange.Offset(counter + 1, 0) = "Total:" & citiesCount 'count the total number of city

End Sub

'https://stackoverflow.com/questions/11109832/how-to-find-if-an-array-contains-a-string

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Upvotes: 1

Related Questions