[已解决]看看我的数据新增与保存的代码错在哪?

tianyonghuai

哪位高手帮我看看附件内数据新增与保存的代码错在哪?

谢谢!!!!

jF5uoykW.rar (55.04 KB, 下载次数: 1)

2009-11-24 16:28 上传

点击文件名下载附件
看看我的数据新增与保存的代码错在哪?

最佳答案

月排行榜 / 总排行榜

雨狐

2009-11-24 21:54

Sub nw新增制令单()
Dim Num%
If Sheet2.[e65536].End(xlUp).Row > 1 Then
    Num = Right(Sheet2.Cells(Sheet2.[e65536].End(xlUp).Row, 5), 6) + 1
Else
    Num = 1
End If
Sheet1.Range("c6") = "SOCX" & Format(Num, "000000")
Range("h6,c8:c11,e8:e11,g8:g11,b14:g17") = ""
End Sub
Sub sv保存制令单()
Dim h%
With Sheet2.Range("e1:e" & Sheet2.[e65536].End(xlUp).Row)
    Set a = .Find(Sheet1.[C6], , , 1)
    If Not a Is Nothing Then
        MsgBox "制令单号：" & Sheet1.[C6] & "已登录查询工作表！", vbInformation
        Sheet1.[h6,c8:c11,e8:e11,g8:g11,b14:g17] = ""
        Call nw新增制令单'这里最好加一个保存后单号自动增加
        Exit Sub
    Else
        For i = 11 To Sheet1.[C18].End(xlUp).Row
            h = Sheet2.[e65536].End(xlUp).Row + 1
            Sheet2.Cells(h, 5) = Sheet1.[C6].Value
            Sheet2.Cells(h, 1) = Sheet1.[h6].Value
            Sheet2.Cells(h, 6) = Sheet1.[c8].Value
            Sheet2.Cells(h, 7) = Sheet1.[e8].Value
            Sheet2.Cells(h, 8) = Sheet1.[g8].Value
            Sheet2.Cells(h, 9) = Sheet1.[c9].Value
            Sheet2.Cells(h, 10) = Sheet1.[e9].Value
            Sheet2.Cells(h, 11) = Sheet1.[g9].Value
            Sheet2.Cells(h, 12) = Sheet1.[c10].Value
            Sheet2.Cells(h, 13) = Sheet1.[e10].Value
            Sheet2.Cells(h, 14) = Sheet1.[g10].Value
            Sheet2.Cells(h, 15) = Sheet1.[c11].Value
            Sheet2.Cells(h, 16) = Sheet1.[e11].Value
            Sheet2.Cells(h, 3) = Sheet1.[g11].Value
            Sheet2.Cells(h, 17) = Sheet1.[b14].Value
            Sheet2.Cells(h, 18) = Sheet1.[d14].Value
            Sheet2.Cells(h, 19) = Sheet1.[f14].Value
            Sheet2.Cells(h, 20) = Sheet1.[g14].Value
            Sheet2.Cells(h, 21) = Sheet1.[b15].Value
            Sheet2.Cells(h, 22) = Sheet1.[d15].Value
            Sheet2.Cells(h, 23) = Sheet1.[f15].Value
            Sheet2.Cells(h, 24) = Sheet1.[g15].Value
            Sheet2.Cells(h, 25) = Sheet1.[b16].Value
            Sheet2.Cells(h, 26) = Sheet1.[d16].Value
            Sheet2.Cells(h, 27) = Sheet1.[f16].Value
            Sheet2.Cells(h, 28) = Sheet1.[g16].Value
            Sheet2.Cells(h, 29) = Sheet1.[b17].Value
            Sheet2.Cells(h, 30) = Sheet1.[d17].Value
            Sheet2.Cells(h, 31) = Sheet1.[f17].Value
            Sheet2.Cells(h, 32) = Sheet1.[g17].Value
           
        Next
        Sheet1.[h6,c8:c11,e8:e11,g8:g11,b14:g17] = ""
    End If
End With
Call nw新增制令单'这里最好加一个保存后单号自动增加
End Sub

PS:通过with 和变量还可以进一步优化

跳转到最佳答案楼层

wsfsy

一、

Sheet1.Range("H6,C8:C11,E8:E11,G8:G11,B14:B17").Select
Selection.ClearContents

二、

  Sheet1.Range("h6,c8:c11,e8:e11,g8:g11,b14:g17").Select
        Selection.ClearContents

wsfsy

或者Sheets(1).Range("H6,C8:C11,E8:E11,G8:G11,B14:B17").ClearContents

 Sheets(1).Range("h6,c8:c11,e8:e11,g8:g11,b14:g17").ClearContents

tianyonghuai

谢谢您的解答！

我改用了您的语句后，1，新增按钮：没有弹错提示了， 但是区域没有清空

                   2，保存按钮：仍然弹出“要求对象”。。。

雨狐

1\Range("h6,c8:c11,e8:e11,g8:g11,b14:g17") = ""

2\ Sheet1.[h6,c8:c11,e8:e11,g8:g11,b14:g17] = ""'没有冒号

雨狐

Sub nw新增制令单()
Dim Num%
If Sheet2.[e65536].End(xlUp).Row > 1 Then
    Num = Right(Sheet2.Cells(Sheet2.[e65536].End(xlUp).Row, 5), 6) + 1
Else
    Num = 1
End If
Sheet1.Range("c6") = "SOCX" & Format(Num, "000000")
Range("h6,c8:c11,e8:e11,g8:g11,b14:g17") = ""
End Sub
Sub sv保存制令单()
Dim h%
With Sheet2.Range("e1:e" & Sheet2.[e65536].End(xlUp).Row)
    Set a = .Find(Sheet1.[C6], , , 1)
    If Not a Is Nothing Then
        MsgBox "制令单号：" & Sheet1.[C6] & "已登录查询工作表！", vbInformation
        Sheet1.[h6,c8:c11,e8:e11,g8:g11,b14:g17] = ""
        Call nw新增制令单'这里最好加一个保存后单号自动增加
        Exit Sub
    Else
        For i = 11 To Sheet1.[C18].End(xlUp).Row
            h = Sheet2.[e65536].End(xlUp).Row + 1
            Sheet2.Cells(h, 5) = Sheet1.[C6].Value
            Sheet2.Cells(h, 1) = Sheet1.[h6].Value
            Sheet2.Cells(h, 6) = Sheet1.[c8].Value
            Sheet2.Cells(h, 7) = Sheet1.[e8].Value
            Sheet2.Cells(h, 8) = Sheet1.[g8].Value
            Sheet2.Cells(h, 9) = Sheet1.[c9].Value
            Sheet2.Cells(h, 10) = Sheet1.[e9].Value
            Sheet2.Cells(h, 11) = Sheet1.[g9].Value
            Sheet2.Cells(h, 12) = Sheet1.[c10].Value
            Sheet2.Cells(h, 13) = Sheet1.[e10].Value
            Sheet2.Cells(h, 14) = Sheet1.[g10].Value
            Sheet2.Cells(h, 15) = Sheet1.[c11].Value
            Sheet2.Cells(h, 16) = Sheet1.[e11].Value
            Sheet2.Cells(h, 3) = Sheet1.[g11].Value
            Sheet2.Cells(h, 17) = Sheet1.[b14].Value
            Sheet2.Cells(h, 18) = Sheet1.[d14].Value
            Sheet2.Cells(h, 19) = Sheet1.[f14].Value
            Sheet2.Cells(h, 20) = Sheet1.[g14].Value
            Sheet2.Cells(h, 21) = Sheet1.[b15].Value
            Sheet2.Cells(h, 22) = Sheet1.[d15].Value
            Sheet2.Cells(h, 23) = Sheet1.[f15].Value
            Sheet2.Cells(h, 24) = Sheet1.[g15].Value
            Sheet2.Cells(h, 25) = Sheet1.[b16].Value
            Sheet2.Cells(h, 26) = Sheet1.[d16].Value
            Sheet2.Cells(h, 27) = Sheet1.[f16].Value
            Sheet2.Cells(h, 28) = Sheet1.[g16].Value
            Sheet2.Cells(h, 29) = Sheet1.[b17].Value
            Sheet2.Cells(h, 30) = Sheet1.[d17].Value
            Sheet2.Cells(h, 31) = Sheet1.[f17].Value
            Sheet2.Cells(h, 32) = Sheet1.[g17].Value
           
        Next
        Sheet1.[h6,c8:c11,e8:e11,g8:g11,b14:g17] = ""
    End If
End With
Call nw新增制令单'这里最好加一个保存后单号自动增加
End Sub

PS:通过with 和变量还可以进一步优化

zsd5237

学习

[em02][em02]

tianyonghuai

THANKS!!!