CODEWITHSUNDEEP
excelvbaspreadsheet
pavlos163
pavlos163

Reputation: 2890

Excel for loop to get value from another row

I have an spreadsheet that contains various data. It looks like this:

   A A A B B C C C C

a  1 2 3 2 1 4 2 3 2

b  0 2 3 3 0 1 2 3 0

c  6 6 3 0 2 1 0 4 0

etc.

What I want is to add all the Aa's and come up with a Aa total, all the Bb's and come up with a Bb total, all the Ab's etc.

What I want to do is, for every column, check if it is A, B or C. I want to do that because the data may change I might end up with four columns for A, two for B, etc. I know however that a, b and c will stay where they are.

I also don't know the order of A, B and C. There could be two A's followed by two C's and then one B.

My final result will be a table containing all the totals:

Aa Ab Ac

Ba Bb Bc

Ca Cb Cc

Where in the previous example would mean that Aa = 1 + 2 + 3 = 6, Ab = 5, etc.

Something like that.

I think the way to go is for 1-1 (the total of Aa's) is to go through every column in the first row. Check if it is an A. If it is, then get the value of the same column but second row. Add it to the total. When gone through all the columns, show up the total in 1-1.

What I have so far (for A):

    Sub getA()
    Dim x As Integer
    Dim total As Integer
    'cols = Find number of columns with data in them
    For x = 1 To cols
        'cell = cell in Ax
        If InStr(1, cellvalue, "a") = 1 Then
            'val = value from row 5 in same column
            total = total + Val
        End If
    Next
End Sub

But I don't really know how to proceed with the commented lines.

Finally, another thing I would like to know is how will these values be presented in their respective cells without any extra event being carried (button for example). They should just appear in their cells from the moment someone opens the spreadsheet.

Any help is greatly appreciated. Thanks.

Upvotes: 0

Views: 127

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

Just an FYI, this can be done using the SUMPRODUCT formula:

=SUMPRODUCT(($B$1:$J$1=D$9)*($A$2:$A$4=$C10)*$B$2:$J$4)

enter image description here

EDIT

To compare the first letter then use this formula:

=SUMPRODUCT((LEFT($B$1:$J$1,1)=D$9)*($A$2:$A$4=$C10)*$B$2:$J$4)

enter image description here

Upvotes: 1

Tim Edwards
Tim Edwards

Reputation: 1028

Are you looking for something like:

Function countletter(strLetter As String) As Double
Dim x As Double, y As Double, xMax As Double, yMax As Double
xMax = Range("A1").CurrentRegion.Columns.Count
yMax = Range("A1").CurrentRegion.Rows.Count
    For x = 1 To xMax
        For y = 1 To yMax
            If Cells(y, x).Value = strLetter Then
                countletter = countletter + 1
            End If
        Next
    Next
End Function

Upvotes: 0

Related Questions