[已解决]用VBA判断（交货期是否超了和进度）

15968725770

用VBA判断（交货期是否超了和进度）

最佳答案

月排行榜 / 总排行榜

cutecpu

2022-11-28 21:32

15968725770 发表于 2022-11-30 12:54
谢谢老师

Sub demo()
  today = Format(Now, "yymmdd")
  m = [m11:m99]
  s = [s11:s99]
  a = [y11:ac99]
  For i = 1 To UBound(a)
    If s(i, 1) = "" Then: GoTo 1
    n = s(i, 1) - a(i, 5)
    a(i, 1) = IIf(m(i, 1) & "" < today, "超期", "沒有超期")
    If n > 0 Then
      a(i, 2) = IIf(n < s(i, 1), "不夠", "零支")
    ElseIf n = 0 Then
      a(i, 2) = "完成"
    Else
      a(i, 2) = "多了" & -n & "支"
    End If
    a(i, 4) = n
1:
  Next
  [y11].Resize(i - 1, 5) = a
End Sub

跳转到最佳答案楼层

cutecpu

15968725770 发表于 2022-11-30 12:54
谢谢老师

Sub demo()
  today = Format(Now, "yymmdd")
  m = [m11:m99]
  s = [s11:s99]
  a = [y11:ac99]
  For i = 1 To UBound(a)
    If s(i, 1) = "" Then: GoTo 1
    n = s(i, 1) - a(i, 5)
    a(i, 1) = IIf(m(i, 1) & "" < today, "超期", "沒有超期")
    If n > 0 Then
      a(i, 2) = IIf(n < s(i, 1), "不夠", "零支")
    ElseIf n = 0 Then
      a(i, 2) = "完成"
    Else
      a(i, 2) = "多了" & -n & "支"
    End If
    a(i, 4) = n
1:
  Next
  [y11].Resize(i - 1, 5) = a
End Sub

cutecpu

Sub demo()
  today = Format(Now, "yymmdd")
  m = [m11:m18]
  s = [s11:s18]
  a = [y11:ac18]
  For i = 1 To UBound(a)
    n = s(i, 1) - a(i, 5)
    a(i, 1) = IIf(m(i, 1) & "" < today, "超期", "沒有超期")
    If n > 0 Then
      a(i, 2) = IIf(n < s(i, 1), "不夠", "零支")
    ElseIf n = 0 Then
      a(i, 2) = "完成"
    Else
      a(i, 2) = "多了" & -n & "支"
    End If
    a(i, 4) = n
  Next
  [y11].Resize(i - 1, 5) = a
End Sub

15968725770

cutecpu 发表于 2022-11-29 08:55
Sub demo()
  today = Format(Now, "yymmdd")
  m = [m11:m18]

谢谢老师改下
合同支数是空的，后面判断为空

cutecpu

15968725770 发表于 2022-11-30 12:03
谢谢老师改下
合同支数是空的，后面判断为空

超出交货日期、进度、未完成支数
三個欄位都 留空白嗎

15968725770

http://www.excelpx.com/forum.php?mod=attachment&aid=NDU2MDIzfGIzMTQ0OTJiMTVkMzk0ZDMzNTRlNTA4NjA5OTkzMDI1fDE3MTM1MTMxNjU%3D&request=yes&_f=.rarcutecpu 发表于 2022-11-30 12:08
超出交货日期、进度、未完成支数
三個欄位都 留空白嗎

谢谢老师