我以为我已经解决了一个问题,但是现在我面临另一个问题……VBA太难了。
For i = 2 To LastRow
For j = 2 To LastCol
'
'*There is lots of code here....*
'
salk = Sheets("SheetS").Cells(i, j).Value
presence = WorksheetFunction.CountIf(Range("B1:D19"), salk)
Dim arrOutput(2)
Select Case presence
Case 1
Sheets("SheetX").Cells(i, j).Value = salk & " (" & GroupCol(salk) & ")"
Case 2
arrOutput(0) = Int(GroupCol(salk) / presence)
arrOutput(1) = GroupCol(salk) - arrOutput(0)
arrOutput(2) = 0
Sheets("SheetX").Cells(i, j).Value = salk & " (" & arrOutput(0) & " - " & arrOutput(1) & ")"
Case Else
MsgBox "Error"
End Select
Next j
Next i
通过上面的代码,我设法获得了以下结果:
我对将奇数分解为两个不同数字的代码感到非常满意。因此,我不必将人员减半。当一个组的结果为偶数时,可以将其划分为两个相同的数。当它很奇怪数则变得非常困难。Dave帮助我确定了奇数。我已经在代码中实现了Dave的代码。您会看到所有括号中的内容都是一个团队中一个组的总人数。您会看到4a4共有39个。由于该组出现了两次,因此我想将该组分成两部分。当我得到18.5分时,Dave(已实现)的代码使我能够获得以下两个数字19和20。我的问题是,在两个组中分别获得这两个单独的数字,团队名称C的组将获得19(请参见图片中的B2),而另一个数字20将获得B6。调整后,我得到以下图像:
因为select的情况是在循环中,所以我不能将它们保留到单独的变量中:(...有没有办法得到我想要的。
我有一个潜在的解决方案(花了我一些时间,算法质量不是很好)
而不是做:
Sheets("SheetX").Cells(i, j).Value = salk & " (" & arrOutput(0) & " - " & arrOutput(1) & ")"
在Case 2
你可以试试(你将不得不去适应我的代码,我试了一个例子的personnal):
' ====== ALGO START
' The following calculate the number of times salk appears before the current cell
Select Case i ' Depends on the ROWS since you CAN'T use a range which is not continuous with COUNTIF!
Case 1
presenceCurr = WorksheetFunction.CountIf(Range("B1" & ":" & Cells(i, j).Address), salk)
Case 2
presenceCurr = WorksheetFunction.CountIf(Range("B2" & ":" & Cells(i, j).Address), salk) _
+ WorksheetFunction.CountIf(Range("B1:D1"), salk)
Case 3
presenceCurr = WorksheetFunction.CountIf(Range("B3" & ":" & Cells(i, j).Address), salk) _
+ WorksheetFunction.CountIf(Range("B1:D2"), salk)
Case 5
' etc ...
' All the other Cases
Case 19
presenceCurr = WorksheetFunction.CountIf(Range("B19" & ":" & Cells(i, j).Address), salk) _
+ WorksheetFunction.CountIf(Range("B1:D18"), salk)
Case Else ' Should not happen
MsgBox "Error, out of range"
End Select
presenceCurr = presenceCurr - 1 ' Minus 1 because the current cell would be counted
' ===== ALGO END
Sheets("SheetX").Cells(i, j).Value = salk & " (" & arrOutput(presenceCurr) & ")"
我不太确定这是否行得通,因为我想"B1:D19"
(与范围有关)与您的嵌套for循环相关联。
如前所述,您可能必须修改此代码。重要的是这个主意:我发现在当前单元格之前找到了salk的次数(在您的示例中为0或1),并将其与数组中的位置相关联:
在Select Case
本来是真的短我你曾经环绕在列则行,但...
注意:这是一个快速而肮脏的代码,可以对我进行明显的优化和分解(例如,使用for
循环而不是select case
)
无论如何,我希望这会有所帮助,您可以用它来做您想要的事情
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句