[算表] vba一行改成多行

楼主: instarfree (Elisa)   2023-05-30 03:45:46
软件:excel
版本:2019
使用chatgpt写VBA
只写一行是正确的
请他运算整张算表就开始错误连篇..
希望版上大大协助
Sub FillVBasedOnOffset()
Dim rngNumbers As Range
Dim rngOutput As Range
Dim targetNumber As Integer
Dim outputCount As Integer
' 设置数字范围和输出范围
Set rngNumbers = Range("I1:AL1")
Set rngOutput = Range("I2:AL2")
' 储存 H2 的数值
Dim h2Value As Variant
h2Value = Range("H2").Value
' 清空输出范围
rngOutput.ClearContents
' 获取目标数字和输出次数限制
targetNumber = Range("H2").Value + 2
outputCount = Range("B2").Value
' 填充V
For Each cell In rngNumbers
If cell.Value = targetNumber Then
If outputCount > 0 Then
cell.Offset(1).Value = "V"
outputCount = outputCount - 1
End If
targetNumber = targetNumber + 3
End If
Next cell
' 恢复 H2 的数值
Range("H2").Value = h2Value
End Sub
https://imgur.com/44RX0oR
实际用VBA操作后2列是正确的
如何改成可以运行在第2列~第58列
目标
开始日期+2则打V 之后每个三天打V(打勾日期+3)
打勾的次数不超过B栏的数字
如果有可以用公式的解法更好(可以在googlesheet上执行)
但是CHATGPT写不太出来...
作者: freePrester (Prester)   2023-05-30 06:45:00
https://i.imgur.com/lWlH8uX.pngC2=IF(AND((COLUMN()-COLUMN($B2))+2-$B2>0,MOD((COLUMN()-COLUMN($B2))-$B2-2,3)=0,INT((COLUMN()-COLUMN($B2)-$B2+1)/3)>0,INT((COLUMN()-COLUMN($B2)-$B2+1)/3)<=IF(ISBLANK($A2),99,$A2)),"V","")修一下… https://i.imgur.com/atjc3JJ.pngC2=IF(AND(C$1+2-$B2>0,MOD(C$1-$B2-2,3)=0,INT((C$1-$B2+1)/3)>0,INT((C$1-$B2+1)/3)<=IF(ISBLANK($A2),99,$A2)),"V","")法一不用看行1的数字,法二要看。没打次数默认99次更正:列1可以再精简,早上脑袋还没醒= ==IF(AND(C$1+2-$B2>0,MOD(C$1-$B2-2,3)=0,(C$1-$B2+1)/3>0,(C$1-$B2+1)/3<=IF(ISBLANK($A2),99,$A2)),"V","")
作者: kinomon (奇诺 Monster)   2023-05-30 09:29:00
也可以用choose搭配阵列溢出https://i.imgur.com/wflIkoB.jpg 类似这样
楼主: instarfree (Elisa)   2023-06-01 15:53:00
谢谢F大跟P大的回复 两个都可以!!

Links booklink

Contact Us: admin [ a t ] ucptt.com