Excel VBA: Use variable containing cell-index in a range

Question

I am very new to macros, so this is probably a dumb question. I have searched and searched, and have not yet found the answer, so I hope an expert in here will aid me :)

In my code I have a large range that I search through for a certain trait (all cells have either the values "Done", "Ongoing" or "Waiting"). When I find the value "Ongoing", I want to use that cell to create a smaller range, that I will use a counter in. I can, however, not make the subrange work :(

Dim range1 As Range

For Each cell In Sheet2.Range("A5:Y5")
    If cell.Value = "Ongoing" Then Set range1 = Cells(cell.Row, cell.Column)
Next cell

i = 0

For Each cell In Sheet2.Range("A5:range1")
    If cell.Value = "Done" Then
        i = i + 1
    End If
Next cell

You are using the ranges wrongly. In any case, what you intend to do with this code will not output what you want even in case of setting the ranges properly. You want to define a smaller range (range1) formed by all the cells whose value equals "Ongoing" (i.e., the whole content of the cell will be "Ongoing"); then you want to iterate through this range to find cells whose value is a different word ("Done"), what will always output the same result (no a single match will be found as far as all these cells have just "Ongoing"). Please, describe clearly what you want to accomplish. — user2480047
– user2480047, Commented Sep 22, 2013 at 11:15
I have about 20 tasks that are executed in chronological order. All the tasks that have been executed will be manually labeled "Done". One task will then be labled "Ongoing" and the future tasks will be labled "Waiting". What I am interested in is to know what task we have come to. That is how many tasks are Done uptill the "Ongoing" task, as I need that information for some other code. I though I could do that by finding the information about which cell contains "Ongoing", and then use a counter, to figure out how many cells are between the first task and the ongoing task. — Esben Eickhardt
– Esben Eickhardt, Commented Sep 22, 2013 at 12:10
Is there a "Classical" list function in excel? One where I can just add and remove items as I please like in Python? — Esben Eickhardt
– Esben Eickhardt, Commented Sep 22, 2013 at 12:15
Yes, you have collection-type variables (Lists, dictionaries, arrays). I will create a simple code for you to help you to continue your development — user2480047
– user2480047, Commented Sep 22, 2013 at 12:18
There you have a code which should help you to understand a bit better the iteration through ranges and array management. Please, let me know if you need any clarification. — user2480047
– user2480047, Commented Sep 22, 2013 at 12:27

score 1 · Accepted Answer · 2013-09-22 12:41:23Z

1

Here you have a simple code performing some of the actions you want:

    Dim rangeToSearch As Range
    Dim doneCount As Integer, onGoingCount As Integer, onGoingDones(50) As Integer
    Set rangeToSearch = Sheet2.Range("A5:Y5")

    doneCount = 0
    onGoingCount = 0
    For Each cell In rangeToSearch
        If (Not IsEmpty(cell)) Then
            If LCase(cell.Value) = "done" Then
                doneCount = doneCount + 1
            ElseIf LCase(cell.Value) = "ongoing" Then
                onGoingCount = onGoingCount + 1
                onGoingDones(onGoingCount) = doneCount
                doneCount = 0
            End If
        End If
    Next cell

It counts the number of "done" (caps does not matter) between "ongoing" cells and store them in an array (onGoingDones; it can just deal with upto 50 elements, but I guess that is more than enough).

Collectives™ on Stack Overflow

Excel VBA: Use variable containing cell-index in a range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related