CODEWITHSUNDEEP
excelvba
wazami
wazami

Reputation: 23

How to loop through cells in a section of columns within a range?

I'm trying to create a planning schedule on Excel. In one sheet there is a section of cells E6:W45 that have the data I wish to loop through. Some cells in this section are populated and some aren't, and I am just checking to see if they do have something in them.

I have managed to do this to calculate the "actual" percentage completed. However, now I am trying to calculate the "planned" percentage complete. To do this I need to first look at the range E4:W4 as each cell in this has a number showing what week it is. For each cell in this range that is less than or equal to what the actual week number is I then want to then loop through cells in the corrosponding columns in other range E6:E45.

E.g. if it is currently week 10. The first thing that will happen is week 10, week 11, week 12... and so on up to week 16 are counted as they have past. Then in the other range I want to loop through ONLY the cells in those columns that were first counted. Does anyone know how I could do this, I have tried lots of things but they dont work.

I think I may need to manually create a new range from outputs in the function. However, I am not sure how to do this. 

Function CalcTarget1(range_data As Range, other_range As Range, weeksOut As Integer)

    Dim cell As Range
    Dim col As Range
    Dim c As Range

    Dim count As Double
    Dim sum As Double
    Dim box As Double

    For Each cell In range_data
        If cell.Style = "Style 1" Then
            count = count + 1
        End If
    Next cell

    If count > 0 Then
        sum = 100 / count
    Else
        sum = 0
    End If

    For Each col In range_data
            If col.Value > weeksOut Then
                For Each c In other_range
                    If c.Style = "Style 1" Then
                          box = box + 1
                     End If
                Next c
        End If
    Next col

    CalcTarget1 = Round(sum, 1) * box & "%"

End Function

This code isn't working right as it returns 0%

Thank you for the time.

Upvotes: 0

Views: 146

Answers (1)

HMVBA
HMVBA

Reputation: 176

Based on your question, I have understood the following steps:

  1. Go through cells E4 to W4 and check the cell value
  2. If the cell value is less than or equal to the week number, then loop through that column rows 6 to 45
  3. Do something with the cell (maybe count it? this step isn't clear in your question. It looks like you know how to count what you want, you just need to know how to do steps 1 and 2 first)

So, I have written a small function which should accomplish this task. It returns the error description if it fails. Just plug in whatever it is you are counting.

Function ColumnLooper(iWeekNum As Long) As Variant
    On Error GoTo errHandler

    'declarations
    Dim iCol As Long, iRow As Long
    Dim myRange As Range

    'initial cell, (top left)
    Set myRange = Range("E4")

    'Check week number
    'by looping through cells E4:W4
    For iCol = 0 To 18
        'Check if the cell value is less than or equal to
        'the week number
        If myRange.Offset(0, iCol).Value <= iWeekNum Then
            'Loop through the corresponding column
            'if the above statement is true (You could
            'also do anything else here)
            For iRow = 2 To 41
                'Do whatever you want here...
                'if you want to reference the specific
                'cell, use myRange.Offset(iRow, iCol)

            Next iRow
        End If
    Next iCol

Exit Function
errHandler:
    'return error
    ColumnLooper = Err.Description
End Function

Upvotes: 2

Related Questions