Question

Excel循环语句的宏设置

设置一个宏,其逻辑为：如果列b-列c<0,则返回1行c列数值；如果列b-列c>0,向下运算成列b-列c-列d，如果仍大于0，则列b-列c-列d-列e，即差和小于0时返回1行和对应列值；如果减到k列仍>0则在a列返回can't use up。

Answer 1

这个是只在第一行运行的
Sub ccc()
Dim b As Integer
Dim c As Integer
Dim i As Integer
Dim d As Integer

b = Range("B1").Value
For i = 3 To 11
c = Cells(1, i).Value
d = Int(Cells(1, i).Value) + d
If b - d < 0 Then
Range("A1").Value = c
Exit For
End If
Next
If Range("A1").Value = "" Then
Range("A1").Value= "Can't usr Up"
End If
End Sub

以下代码是可以在多行运行的
Sub ddd()
Dim b As Integer, c As Integer, d As Integer
Dim i As Long, j As Long
j = Range("B65536").End(xlUp).Row
For j = 1 To j
b = Cells(j, 2).Value
d = 0
For i = 3 To 11
c = Cells(j, i).Value
d = Int(Cells(j, i).Value) + d
If b - d < 0 Then
Cells(j, 1).Value = c
Exit For
End If
Next
If Cells(j, 1).Value = "" Then
Cells(j, 1).Value = "Can't usr Up"
End If
Next
End Sub

Answer 2

*EXCEL语言我不会，但把我的设计思路说出来。
*定义变量：X，Y
X=0
Y=0

X=B1
FOR I=1 TO 9 *(从B1-C1到K1共需9次)
IF X-OFFSET(B1,,I,)<0
A1=OFFSET(B1,,I,)
EXIT(退出循环)
ELSE
X=X-OFFSET(B1,,I,)
Y=Y+1
ENDIF
ENDFOR

IF Y>=9
A1="can't use up"
ENDIF

Answer 3

似乎公式就可完成，不必VBA