Office中国论坛/Access中国论坛

标题: [求助]修改代码 [打印本页]

---

作者: mengdini    时间: 2006-10-30 19:01
标题: [求助]修改代码
Sub 刷新()
For n = 8 To 65536

bm = Application.WorksheetFunction.SumIf(Worksheets("下单").Range("$AB"), Worksheets("单据输入").Range("C" & n), Worksheets("下单").Range("B:B"))
mc = Application.WorksheetFunction.VLookup(Worksheets("单据输入").Range("E" & n), Worksheets("下单").Range("$BC"), 2, False)
gg = Application.WorksheetFunction.VLookup(Worksheets("单据输入").Range("E" & n), Worksheets("下单").Range("$BD"), 3, False)
dw = Application.WorksheetFunction.VLookup(Worksheets("单据输入").Range("E" & n), Worksheets("下单").Range("$B:$E"), 4, False)
dj = Application.WorksheetFunction.Index(Worksheets("工价").Range("$F:$I"), Application.WorksheetFunction.Match(Worksheets("单据输入").Range("C" & n), Worksheets("工价").Range("$A:$A"), 0), Application.WorksheetFunction.Match(Worksheets("单据输入").Range("I" & n), Worksheets("工价").Range("$F$1:$I$1"), 0))
je = Worksheets("单据输入").Range("J" & n) * Worksheets("单据输入").Range("L" & n)
    Cells(n, 13) = je
    Cells(n, 5) = bm
    Cells(n, 6) = mc
    Cells(n, 7) = gg
    Cells(n, 8) = dw
    Cells(n, 12) = dj
Next n
End Sub

没办法判断"单据输入"工作表为C列"某一行"为空时，停止执行"刷新()"的程序

没办法判断"单据输入"工作表为E列"某一行"为空时，停止执行mc\gg\dw\dj\je的程序

没办法判断"单据输入"工作表为I列"某一行"、J列"某一行"只要有一个为空时，停止执行dj/je的程序

请大家帮忙？？？？

[此贴子已经被作者于2006-10-30 11:05:31编辑过]

---

作者: 方漠    时间: 2006-11-1 21:28
Sub 刷新()
For n = 8 To 65536

If len(trim(sheets("单据输入").range("C"&n)))=0 then end  'Or Exit For

bm = Application.WorksheetFunction.SumIf(Worksheets("下单").Range("$AB"), Worksheets("单据输入").Range("C" & n), Worksheets("下单").Range("B:B"))

If len(trim(sheets("单据输入").range("E"&n)))<>0 then
mc = Application.WorksheetFunction.VLookup(Worksheets("单据输入").Range("E" & n), Worksheets("下单").Range("$BC"), 2, False)
gg = Application.WorksheetFunction.VLookup(Worksheets("单据输入").Range("E" & n), Worksheets("下单").Range("$BD"), 3, False)
dw = Application.WorksheetFunction.VLookup(Worksheets("单据输入").Range("E" & n), Worksheets("下单").Range("$B:$E"), 4, False)

If len(trim(sheets("单据输入").range("I"&n)))<>0 and len(trim(sheets("单据输入").range("J"&n)))<>0 then


dj = Application.WorksheetFunction.Index(Worksheets("工价").Range("$F:$I"), Application.WorksheetFunction.Match(Worksheets("单据输入").Range("C" & n), Worksheets("工价").Range("$A:$A"), 0), Application.WorksheetFunction.Match(Worksheets("单据输入").Range("I" & n), Worksheets("工价").Range("$F$1:$I$1"), 0))
je = Worksheets("单据输入").Range("J" & n) * Worksheets("单据输入").Range("L" & n)

End If

End If


    Cells(n, 13) = je
    Cells(n, 5) = bm
    Cells(n, 6) = mc
    Cells(n, 7) = gg
    Cells(n, 8) = dw
    Cells(n, 12) = dj
Next n
End Sub

---

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)

Powered by Discuz! X3.3