CODEWITHSUNDEEP
excelvba
DommyCastles
DommyCastles

Reputation: 425

Count cell values in corresponding cell based on the value of another cell in the same row

I have a program where it counts the number of cells based on a value.

For example, if a cell value in column E = "75", add to the "Air Count", else if column E = "76", add to the "Road Count"

In my data set there is a corresponding cell in column S, with the number of items. I would like to count those items based on their values in Column E. For example, if the cell in column E = "75", then check the corresponding cell in column S, and add it to an "Air Item Count".

I think I'm a bit confused as to where to add this in. I have attempted to add it in, but I haven't got it correct so I just commented it out.

Here is my code:

Sub ParseData()

Dim airConCount As Integer, airItemCount As Integer, roadConCount As Integer, roadItemCount As Integer, totalConCount As Integer, _
totalItemCount As Integer

'Find last value in row
Dim LastSVS As Integer
Dim LastRowITEMS As Integer


LastRowSVS = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
'LastRowITEMS = ActiveSheet.Cells(Rows.Count, 19).End(xlUp).Row

Dim cel As Range
Dim cel2 As Range
Dim airCounter As Integer
Dim roadCounter As Integer

airCounter = airCounter + 1
roadCounter = roadCounter + 1

'CONSIGNMENT NOTE COUNT
    For Each cel In Range("E2:E" & LastRowSVS)

        If cel.Value = "75" Or cel.Value = "48N" Or cel.Value = "15N" Or cel.Value = "29" Or cel.Value = "701" _
        Or cel.Value = "15D" Or cel.Value = "EP3" Or cel.Value = "X12" Or cel.Value = "753" Or cel.Value = "EP5" _
        Or cel.Value = "X12" Or cel.Value = "753" Or cel.Value = "EP5" Or cel.Value = "1" Or cel.Value = "4" _
        Or cel.Value = "INT" Or cel.Value = "17B" Or cel.Value = "73" Then

        airCounter = airCounter + 1

        Set cel2 = Range("S2:S" & airCounter)


        airConCount = airConCount + 1
        'airItemCount = airItemCount + cel2.Item(19)



        ElseIf cel.Value = "76" Then

        roadConCount = roadConCount + 1
        Set cel2 = Range("J2:J" & roadCounter)
        'roadItemCount = roadItemCount + cel2.Item(19)


        End If

    Next cel

Here is a screenshot of my dataset. dataset

I would like the Items counted, if they match an attribute in Service. So, if the Service is 75, count all items in the corresponding row.

Upvotes: 0

Views: 407

Answers (1)

WSC
WSC

Reputation: 993

I think this does what you need it to do.

It loops through all the cells in the E2:E range and depending on if the value is 76 it adds the value of the cell in column S of the same row to roadItemCount. If the value is anything else, it does the same logic but adds the value to airItemCount.

I've inversed your if/else logic so if you really need to select those specific values in column E you can use your logic.

Sub ParseData()

Dim airConCount As Integer, airItemCount As Integer, roadConCount As Integer, roadItemCount As Integer, totalConCount As Integer, _
totalItemCount As Integer

'Find last value in row
Dim LastSVS As Integer
Dim LastRowITEMS As Integer


LastRowSVS = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
LastRowITEMS = ActiveSheet.Cells(Rows.Count, 19).End(xlUp).Row

Dim cel As Range
Dim airCounter As Integer
Dim roadCounter As Integer

airCounter = airCounter + 1
roadCounter = roadCounter + 1

'CONSIGNMENT NOTE COUNT
For Each cel In Range("E2:E" & LastRowSVS)

    If cel.Value = "76" Then
        roadConCount = roadConCount + 1
        roadItemCount = roadItemCount + ActiveSheet.Cells(cel.Row, 19).Value

        'MsgBox ("roadConCount: " & roadConCount)
        'MsgBox ("roadItemCount: " & roadItemCount)
    Else
        airConCount = airConCount + 1
        airItemCount = airItemCount + ActiveSheet.Cells(cel.Row, 19).Value

        'MsgBox ("airConCount: " & airConCount)
        'MsgBox ("airItemCount: " & airItemCount)
    End If

Next cel

MsgBox ("airItemCount: " & airItemCount)
MsgBox ("roadItemCount: " & roadItemCount)

End Sub

Upvotes: 1

Related Questions