Optimizing loop / nested loop

Question

I'm working my way through loops and naturally have started off with a challenging one! I have a workbook with multiple sheets. Each sheet has the operations to complete a "widget". I'm trying to walk through a range of cells and search by date to find a matching date. If that date matches, I want to add the std hours in row 7 of that column. I was able to make this work through brut force code and copy and paste my loop for each column. I REALLY DONT want to do this for all the columns on each tab.

I'm sure there is a way to use my counters for last row and last column to do a nested loop so once I complete the loop in one column, it moves to the next. I'm just not sure how to get there. Was hoping for some help on this! Thank you!

edit: essentially what I want to do is start in I12, loop to bottom of column looking for the date then counting the number of times I see that to add up the number of PPC hours (I7). Then, move to J12, loop to bottom of column, move to K12, loop to bottom adding up hours for Assy. Etc...

Sub Resource_Overview()
'Summary of daily tasks by worktype

'Declare the variables we'll need
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim i As Double 'for counters, using double to add up decimals
Dim Assy, Solder, QC, Weld, Test, PPC As Double 'variables to hold std hours total
Dim a As Long

Assy = 0#
Solder = 0#
QC = 0#
Weld = 0#
Test = 0#
PPC = 0#

'Find Last Row and Column
Set sht = ActiveSheet
Set StartCell = Range("I12")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info

Set sht = ActiveSheet
Set StartCell = Range("I12")
  
    For i = 12 To LastRow
        If Range("I" & i).Value = 44154 Then
            If Range("I" & 1) = "Assy" Then
                Assy = Assy + Range("I7").Value
            ElseIf Range("I" & 1) = "Solder" Then
                Solder = Solder + Range("I7").Value
            ElseIf Range("I" & 1) = "Weld" Then
                Weld = Weld + Range("I7").Value
            ElseIf Range("I" & 1) = "Test" Then
                Test = Test + Range("I7").Value
            ElseIf Range("I" & 1) = "PPC" Then
                PPC = PPC + Range("I7").Value
            ElseIf Range("I" & 1) = "QC" Then
                QC = QC + Range("I7").Value
            End If
        End If
    Next i
        
        
    For i = 12 To LastRow
        If Range("J" & i).Value = 44154 Then
            If Range("J" & 1) = "Assy" Then
                Assy = Assy + Range("J7").Value
            ElseIf Range("J" & 1) = "Solder" Then
                Solder = Solder + Range("J7").Value
            ElseIf Range("J" & 1) = "Weld" Then
                Weld = Weld + Range("J7").Value
            ElseIf Range("J" & 1) = "Test" Then
                Test = Test + Range("J7").Value
            ElseIf Range("J" & 1) = "PPC" Then
                PPC = PPC + Range("J7").Value
            ElseIf Range("J" & 1) = "QC" Then
                QC = QC + Range("J7").Value
            End If
        End If
    Next i
    
        For i = 12 To LastRow
        If Range("K" & i).Value = 44154 Then
            If Range("K" & 1) = "Assy" Then
                Assy = Assy + Range("K7").Value
            ElseIf Range("K" & 1) = "Solder" Then
                Solder = Solder + Range("K7").Value
            ElseIf Range("K" & 1) = "Weld" Then
                Weld = Weld + Range("K7").Value
            ElseIf Range("K" & 1) = "Test" Then
                Test = Test + Range("K7").Value
            ElseIf Range("K" & 1) = "PPC" Then
                PPC = PPC + Range("K7").Value
            ElseIf Range("K" & 1) = "QC" Then
                QC = QC + Range("K7").Value
            End If
        End If
    Next i
    
        For i = 12 To LastRow
        If Range("L" & i).Value = 44154 Then
            If Range("L" & 1) = "Assy" Then
                Assy = Assy + Range("L7").Value
            ElseIf Range("L" & 1) = "Solder" Then
                Solder = Solder + Range("L7").Value
            ElseIf Range("L" & 1) = "Weld" Then
                Weld = Weld + Range("L7").Value
            ElseIf Range("L" & 1) = "Test" Then
                Test = Test + Range("L7").Value
            ElseIf Range("L" & 1) = "PPC" Then
                PPC = PPC + Range("L7").Value
            ElseIf Range("L" & 1) = "QC" Then
                QC = QC + Range("L7").Value
            End If
        End If
    Next i

        For i = 12 To LastRow
        If Range("M" & i).Value = 44154 Then
            If Range("M" & 1) = "Assy" Then
                Assy = Assy + Range("L7").Value
            ElseIf Range("M" & 1) = "Solder" Then
                Solder = Solder + Range("M7").Value
            ElseIf Range("M" & 1) = "Weld" Then
                Weld = Weld + Range("M7").Value
            ElseIf Range("M" & 1) = "Test" Then
                Test = Test + Range("M7").Value
            ElseIf Range("M" & 1) = "PPC" Then
                PPC = PPC + Range("M7").Value
            ElseIf Range("M" & 1) = "QC" Then
                QC = QC + Range("M7").Value
            End If
        End If
    Next i
    
    Sheets("Sheet1").Select
    Range("B2") = PPC
    Range("B3") = Assy
    Range("B4") = Solder
    Range("B5") = QC
    
End Sub

Wizhi · Accepted Answer · 2020-10-06 22:10:26Z

1

So you can build your range:

Range(A1:D1) -> Range(Cells(A1), Cells(D1)) -> 

Range(Cells(row number, column number), Cells(row number, column number)) -> 

Range(Cells(1, 1), Cells(1, 4))

If the range is "A1". We can write either Range(Cells(1, 1), Cells(1, 1)) or use cell reference Cells(1,1).

To build ranges with loop you can replace some of the numbers with letters that represent the loop value, i.e. column/row number.

Without testing but I think you will get the logic:

Sub Resource_Overview()
'Summary of daily tasks by worktype

'Declare the variables we'll need
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim i As, j As Long 'I always use long
Dim Assy, Solder, QC, Weld, Test, PPC As Double 'variables to hold std hours total
Dim a As Long

Assy = 0#
Solder = 0#
QC = 0#
Weld = 0#
Test = 0#
PPC = 0#

'Find Last Row and Column
Set sht = ActiveSheet
Set StartCell = Range("I12")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info

Set sht = ActiveSheet

For i = 9 To LastColumn 'Set from which column number you want the loop to start from
    For j = 12 To LastRow
        If Cells(j,i).Value = 44154 Then
            If Cells(1,i).Value = "Assy" Then
                Assy = Assy + Cells(7,i).Value
            ElseIf Cells(1,i).Value = "Solder" Then
                Solder = Solder + Cells(7,i).Value
            ElseIf Cells(1,i).Value = "Weld" Then
                Weld = Weld + Cells(7,i).Value
            ElseIf Cells(1,i).Value = "Test" Then
                Test = Test + Cells(7,i).Value
            ElseIf Cells(1,i).Value = "PPC" Then
                PPC = PPC + Cells(7,i).Value
            ElseIf Cells(1,i).Value = "QC" Then
                QC = QC + Cells(7,i).Value
            End If
        End If
    Next j
Next i

    
Sheets("Sheet1").Range("B2") = PPC
Sheets("Sheet1").Range("B3") = Assy
Sheets("Sheet1").Range("B4") = Solder
Sheets("Sheet1").Range("B5") = QC

End Sub

answered Oct 6, 2020 at 22:10

Wizhi

6,5894 gold badges29 silver badges49 bronze badges

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

6 Comments

deiblerj Over a year ago

This worked perfectly! Took me a few times reading over it, but I understand it now. Thank you so much, this is a huge help!

Wizhi Over a year ago

Glad it worked :). Feel free to ask if you have any further questions or want any more details. Yes, first time I made loops it was a challenge to get the concept, but with training you'll master it!. Feel free to accept the answer if you think it solved your question/issue How does accepting an answer work?. Wish you happy coding!!

deiblerj Over a year ago

as soon as my reputation gets to 15 I'll definitely give the answer a like +1. I do have another question that involves adding another loop to my code. Where I call out the date in text format (44154), I would like to pull that from say Sheet1, and have Cell B1 be a date, B2 the next day, B3 the next day, etc. till I get to a full week (7 days). I tried playing around with adding another loop but it kept skipping over the main loop that we just solved, lol. I think its getting messed up because I'm trying to reference a different sheet, maybe?

Wizhi Over a year ago

Thanks! I would suggest you a new question (and maybe some mock data as date can get more tricky...), but yes you are right about sheet reference. You don't refer to sheets, which cause "issues"/unstable code between sheets, since the code doesn't know which sheet you refer too (i.e. Cells(j,i).Value -> ws.Cells(j,i).Value). My initial thought is to do a third loop where you loop the date column/range (i.e. after For j, do For k) and then you check Cells(j,i).Value = 44154 -> Cells(j,i).Value = Cells(k,"B").Value. Depending on your setup, other solutions might be more effective :)

deiblerj Over a year ago

thanks! I'll try playing around with that and post another question if I get stuck.

|

Collectives™ on Stack Overflow

Optimizing loop / nested loop

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related