VBA Excel: IF then Statements running loops

Question

I am new to VBA but reading books to improve. At the moment, I am taking columns from column "A" and using them as identifiers to run a IF ELSEIF statement in another column.

Basically in range(A1:A3) the value "ERIC" will exist in each cell [A1 = ERIC, A2 = ERIC...] and in range(B1:B3) will be three different integer values [B1 = 2, B2 = 9...]. I need to find the Greater of those integers for range "ERIC" and put the Greatest value for range "ERIC" in cell(C1).

Then repeat the process in range(A4:A6) for value "Sally" which correlates to integer range(B4:B6) [B4 = 1, B5 = 4...] . The greatest value would then go into cell(C4) I have about 40 names.

Please help. Thanks.

so what have you tried till now

DevelopmentIsMyPassion
– DevelopmentIsMyPassion

2013-03-12 19:16:40 +00:00
Commented Mar 12, 2013 at 19:16 — DevelopmentIsMyPassion
– DevelopmentIsMyPassion, Commented Mar 12, 2013 at 19:16

StoriKnow · Accepted Answer · 2013-03-13 16:32:38Z

2

This should do as you ask. It makes the assumptions that you're on Sheet 1, your names are in Column A, values in Column B.

         Public Sub FindNameAndGreatestValue()
    Dim nameColumnRowCount As Integer
    Dim nameColumn As Integer
    Dim valueColumn As Integer
    Dim outputColumn As Integer
    Dim currentName As String

    nameColumnRowCount = Cells(Rows.Count, 1).End(xlUp).Row
    currentName = ""
    nameColumn = 1     '1 = A - change this to column that has names
    valueColumn = 4    '4 = D - change this to the column that has values
    outputColumn = 5   '5 = E - change this to column that should contain output
    Dim currentLargestForName As Integer
    Dim currentNameStartRow As Integer
    currentLargestForName = -999
    currentName = Cells(1, nameColumn).Value
    currentNameStartRow = 1

    Dim currentRow As Integer
    For currentRow = nameColumn To nameColumnRowCount + 1
        'if last known name is the same as the current row's name
        If StrComp(currentName, Cells(currentRow, nameColumn).Value, vbTextCompare) = 0 Then
            'if current rows number is larger than the last known largest number
            If currentLargestForName < CInt(Cells(currentRow, valueColumn).Value) Then
                currentLargestForName = CInt(Cells(currentRow, valueColumn).Value)
            End If
        Else
            'drop into here if the names no longer match, meaning a new name was found.
            'output the largest known number from the previous name into the first row of that name
            Cells(currentNameStartRow, outputColumn).Value = currentLargestForName
            currentNameStartRow = currentRow    'save the row this new name starts at for number output later
            currentLargestForName = CInt(Cells(currentRow, valueColumn).Value)
            currentName = Cells(currentRow, nameColumn).Value
        End If
    Next
End Sub

BEFORE

enter image description here

AFTER

enter image description here

edited Mar 13, 2013 at 16:32

answered Mar 12, 2013 at 21:19

StoriKnow

5,8676 gold badges40 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

StoriKnow Over a year ago

I think you should submit an answer then so we can all learn ;)

Our Man in Bananas Over a year ago

hey I gave you a +1! It'd take me ages to write an array forula to do what your code does :)

user2162467 Over a year ago

Thanks [especially for the comments in the code, helps to follow your logic], this code works very well except for one small issue... All the names/numbers will process except for the last one. I can't figure out why it wont work, not matter what the namecount is.

StoriKnow Over a year ago

Oops - I apologize, that was my fault. I've made a change to the code by adding + 1 to the line For currentRow = nameColumn to nameColumnRowCount. It should work now.

StoriKnow Over a year ago

Glad to help. If you're curious, the reason that worked is because we needed to go one past the last used row (e.g. row 14) in order to set off the conditional statement where the names no longer match, which then outputs the largest number as expected. Best of luck with the rest of your project.

|

Collectives™ on Stack Overflow

VBA Excel: IF then Statements running loops

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related