arrays - 优化循环/嵌套循环
问题描述
我正在循环中工作,自然而然地从一个具有挑战性的开始!我有一个包含多张工作表的工作簿。每个工作表都有完成“小部件”的操作。我正在尝试遍历一系列单元格并按日期搜索以找到匹配的日期。如果该日期匹配,我想在该列的第 7 行添加标准时间。我能够通过强力代码完成这项工作,并为每一列复制并粘贴我的循环。我真的不想为每个选项卡上的所有列执行此操作。
我确信有一种方法可以使用我的最后一行和最后一列的计数器来执行嵌套循环,所以一旦我在一个列中完成循环,它就会移动到下一个。我只是不确定如何到达那里。希望对此有所帮助!谢谢!
编辑:基本上我想要做的是从 I12 开始,循环到列底部查找日期,然后计算我看到的次数以将 PPC 小时数 (I7) 相加。然后,移动到 J12,循环到列的底部,移动到 K12,循环到底部,为 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
解决方案
所以你可以建立你的范围:
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))
如果范围是“A1”。我们可以编写Range(Cells(1, 1), Cells(1, 1))
或使用单元格引用Cells(1,1)
。
要使用循环构建范围,您可以将一些数字替换为代表循环值的字母,即列/行号。
没有测试,但我认为你会得到逻辑:
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
推荐阅读
- android - 如何在 Delphi FMX Android 平台上通过 Sip 和 SipManager 拨打电话?
- sql - 当一个表ID存在于另一表的多个列中时,如何编写SQL JOIN查询以获取结果
- google-maps - 在谷歌地图上触发折线点击时更改折线颜色
- javascript - 无法将多个值/数据推送到 angular 2-tree
- javascript - 识别 div 中的字符并添加类 javascript
- jquery - 引导箱模式未设置“选定”选择列表项
- javascript - 我怎样才能让我的广告适合身体宽度
- jmeter - 无法使用 JMeter 测试脚本记录器记录在 Internet Explorer 中本地主机上运行的应用程序
- android - 显示 QWERTY 键盘时显示线性布局冲突
- java-8 - Desktop.getDesktop.open() 调用后不退出 JVM