<p>Sub save()<br/> Application.ScreenUpdating = False<br/> Dim i As Long, j As Integer, row1 As Long, row2 As Integer<br/> If Sheets("INVOICING").Range("D3") = "" Then<br/> MsgBox "INVOICING NUMBER not null!", 48, "ADVERT"<br/> Exit Sub<br/> End If<br/> row1 = Sheets("INVOICING DATA").Range("A65536").End(xlUp).Row<br/> For i = 1 To row1<br/> If Sheets("INVOICING DATA").Cells(i, 1) = Sheets("INVOICING").Range("D3") Then<br/> MsgBox "INVOICING NUMBER repeat!", 16, "ADVERT"<br/> Application.ScreenUpdating = True<br/> Exit Sub<br/> End If<br/> Next<br/> With Sheets("INVOICING")<br/> If .Range("C10") <> "" Then<br/> row2 = 10<br/> Else<br/> row2 = .Range("C10").End(xlUp).Row<br/> End If<br/> For j = 7 To row2<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 1) = .Range("D3")<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 2) = .Range("D4")<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 3) = .Range("D5")<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 4) = .Range("G3")<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 5) = .Range("C" & j)<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 6) = .Range("D" & j)<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 7) = .Range("E" & j)<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 8) = .Range("F" & j)<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 9) = .Range("G" & j)<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 10) = .Range("G12")<br/> Sheets("INVOICING DATA").Cells(j - 6 + row1, 11) = .Range("D12")<br/> Next<br/> Application.EnableEvents = False<br/> .Range("D3:D5").ClearContents<br/> .Range("C7:G10").ClearContents<br/> .Range("G3") = VBA.Date<br/> End With<br/> MsgBox "OK!", 64, "ADVERT"<br/> Sheets("INVOICING").Range("E9").Select<br/> Application.EnableEvents = True<br/> Application.ScreenUpdating = True<br/>End Sub<br/>rivate Sub Worksheet_Change(ByVal Target As Range)<br/>Application.EnableEvents = False</p><p>On Error Resume Next<br/>If Target.Row > 6 And Target.Row < 11 Then<br/> If Target.Column = 6 Then<br/> [F11] = Application.WorksheetFunction.Sum([F6:F10])<br/> ElseIf Target.Column = 7 Then<br/> [G11] = Application.WorksheetFunction.Sum([G6:G10])<br/> ElseIf Target.Column = 3 Then<br/> Cells(Target.Row, 4) = Application.WorksheetFunction.VLookup(Cells(Target.Row, 3), Sheets("ITEM").Range("A2:C" & Sheets("ITEM").Range("A65536").End(xlUp).Row), 2, 0)<br/> Cells(Target.Row, 5) = Application.WorksheetFunction.VLookup(Cells(Target.Row, 3), Sheets("ITEM").Range("A2:C" & Sheets("ITEM").Range("A65536").End(xlUp).Row), 3, 0)<br/> End If<br/>ElseIf Target.Column = 4 And Target.Row = 4 Then<br/> [D5] = Application.WorksheetFunction.VLookup([D4], Sheets("BUYERS").Range("A2:B" & Sheets("BUYERS").Range("A65536").End(xlUp).Row), 2, 0)<br/>End If<br/>Application.EnableEvents = True<br/>End Sub<br/>rivate Sub Workbook_Open()<br/>Application.EnableEvents = False<br/>Sheets("INVOICING").Range("G3") = VBA.Date<br/>Sheets("INVOICING").Range("D3:D5").ClearContents<br/>Sheets("INVOICING").Range("D3:D5").ClearContents<br/>Application.EnableEvents = True<br/>End Sub</p><p>偶得E文水平太菜,提示语句自己改吧[em04][em04]</p><p>三段描述没有看明白意思,等空的时候再来看看吧</p><p><br/></p><p> </p><p><font color="#ff0000">虽然没完全符合要求,但小戏同学作为第一个响应者,奖励两个金币</font></p><p><font color="#ff0000"> -----ldy</font><br/></p>
[此贴子已经被ldy于2007-5-13 14:12:42编辑过] |