Sub 查詢系統(tǒng)()
Dim Crow, m, k As Integer
Dim rng As Range
ends = Sheet2.Columns(1).Find("*", , , , , xlPrevious).Row ''對第一列 從下往上查找 找到A列的最后一個單元格所在行
Crow = Application.CountA(Sheet2.Range("e:e")) ''''E列非空單元格的行數(shù)
Sheet2.Range("E3:G" & Crow + 2).Delete ''清除之前的結(jié)果
For Each rng In Sheet2.Range("a2:a" & ends)
m = m + 1
If rng Like Sheet2.Range("f1") Then '比較
k = k + 1
Sheet2.Range("a" & m + 1 & ":c" & m + 1).Copy Sheet2.Range("e" & k + 2 & ":g" & k + 2)
''把所在記錄的行復(fù)制到
End If
Next
End Sub
'''入庫單保存
Sub 入庫單計算()
Dim es, rng As Range
Dim Saddress As String
Set es = Sheet1.Columns(3).Find("*", , xlFormulas, , , xlPrevious) '''查找最后個單元格
Saddress = es.address
ends = Sheet1.Range("c:c").Find("*", , , , , xlPrevious).Row '''最后單元的所在的行
Range([c5], Saddress).Select
If ends = 4 Then ''如果記錄是從第4行是表列名 ,,就沒有數(shù)據(jù)
MsgBox "請您先添加記錄!", vbCritical, "錯誤提示"
Exit Sub
End If
For Each rng In Range([c5], es)
rng.Offset(0, 2) = rng * rng.Offset(0, 1) ''用offset(偏移行,偏移列)計算
Next
End Sub
Sub 入庫單開單()
Dim Stime As String
Dim Scolumn As Range
Dim Aint, Bint As Integer
Set Scolumn = Cells.Find("*", , xlFormulas, , , xlPrevious) '''查找最后個單元格
Xcolumn = Cells.Find("*", , xlFormulas, , , xlPrevious).Row '''查找最后個單元格所在的行
''Set Scolumn = Range("e:e").Find("*", , xlFormulas, , , xlPrevious) '''查找該列的最后個單元格 xlFormulas單元格的公式 xlPrevious向上查找 ''查到的是個range對象
''s = Scolumn.address ''最后個單元格
''Range("a5", Scolumn.Offset(5)) = "" ''當(dāng)最后個單元格沒有數(shù)據(jù)的時候會少清空行
If Xcolumn > 4 Then ''防止把列名清空
Range("a5", Cells(Xcolumn, 5)) = ""
End If
Stime = Format(Now, "YYMMDDHHss")
Range("b2") = "SM" + Stime ''單號
Range("e2") = ""
End Sub
Sub 入庫保存()
Dim rng As Range
Dim str As String
Dim Sint, Bint As Integer
Dim c, Csheet3 As String
Dim rngs As Range
Set rngs = Sheet3.Range("a:a").Find(Sheet1.Range("b2")) ';對A列單號進(jìn)行搜索
If Not rngs Is Nothing Then '''判斷是否存在記錄
MsgBox "已存在記錄!!"
Exit Sub
End If
'If Range("b2").Find(Sheet1.Range("b2")) = Sheet1.Range("b2") Then ''在工作表sheet2中 F列查找 b2單元格內(nèi)容
'MsgBox "你已經(jīng)保存過了,請查詢", vbCritical, "重復(fù)保存提示"
'Exit Sub ''如果相等退出
'End If
Set rng = Cells.Find("*", , xlFormulas, , , xlPrevious) ''查找最后個單元格
str = rng.address
Sint = rng.Row ''最后單元格所在的行數(shù)
If Sint = 4 Then
MsgBox "沒有記錄需要保存!", vbInformation, "提示"
Exit Sub
End If
Bint = Cells.Find("*", , xlFormulas, , , xlPrevious).Row '''sheet1的記錄行數(shù)
Csheet3 = Application.CountA(Sheet3.Range("a:a")) '''sheet3工作表已使用的行數(shù)
Range("a5:e" & Sint).Select
Range("a5:e" & Sint).Copy Sheet3.Range("c" & Csheet3 + 1 & ":g" & Csheet3 + Sint - 4) ''記錄復(fù)制過去
Sheet3.Range("a" & Csheet3 + 1).Resize(Bint - 4).Value = [b2] ''保存單號 A列單元格向下偏移
Sheet3.Range("b" & Csheet3 + 1).Resize(Bint - 4) = [e2] ''保存廠商 A列單元格向下偏移
Sheet3.Range("h" & Csheet3 + 1).Resize(Bint - 4) = Now() ''保存日期 A列單元格向下偏移
End Sub
