VBA常用命令

最新推荐文章于 2024-07-08 15:23:26 发布

QOOQC

最新推荐文章于 2024-07-08 15:23:26 发布

阅读量139

点赞数

文章标签：经验分享

本文链接：https://blog.csdn.net/qq_43214142/article/details/133618982

版权

Sub SADSAFSA()
Application.ScreenUpdating = False
Dim bzjpzWb As Workbook
bzjpz = ThisWorkbook.Path & "\模板.xls"
Set bzjpzWb = GetObject(bzjpz)
bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
For i = 2 To bzjpzWbrow
    bzjpzWb.Sheets(1).Rows(i).Clear
Next i
thiswbcrow = ThisWorkbook.Sheets(1).Range("c65536").End(xlUp).Row
For i = 2 To thiswbcrow
    ThisWorkbook.Sheets(1).Cells(i, 3).Clear
Next i
Application.Windows(bzjpzWb.Name).Visible = True
bzjpzWb.Close True
Set bzjpzWb = Nothing
End Sub

Sub SDSDFSDFSD()
Application.ScreenUpdating = False
qujian = InputBox("请输入期间")
zhidanr = InputBox("请输入制单人姓名", "亲，输下名字呗！")
Dim crjcxWb As Workbook
crjcx = ThisWorkbook.Path & "\ADSAD" & qujian & ".xls"
Set crjcxWb = GetObject(crjcx)
crjcxWbrow = crjcxWb.Sheets(1).Range("a65536").End(xlUp).Row

Set datesymboldic = CreateObject("Scripting.Dictionary")
For i = 3 To crjcxWbrow - 1
    If Not datesymboldic.exists(crjcxWb.Sheets(1).Cells(i, 5).Value) Then
        datesymboldic.Add crjcxWb.Sheets(1).Cells(i, 5).Value, crjcxWb.Sheets(1).Cells(i, 5).Value
    End If
Next i
daynum = datesymboldic.Count
daystr = datesymboldic.items
thiswbhrow = ThisWorkbook.Sheets(1).Range("h65536").End(xlUp).Row
thiswbrow = ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
bankqnum = ThisWorkbook.Sheets(1).Range("q65536").End(xlUp).Row
Set zzdic = CreateObject("Scripting.Dictionary")
For i = 2 To thiswbrow
    If Not zzdic.exists(ThisWorkbook.Sheets(1).Cells(i, 2).Value) Then
        zzdic.Add ThisWorkbook.Sheets(1).Cells(i, 2).Value, ThisWorkbook.Sheets(1).Cells(i, 2).Value
    End If
Next i
zzname = zzdic.items
zznum = zzdic.Count
Set dqdic = CreateObject("Scripting.Dictionary")
For i = 2 To thiswbrow
    If Not dqdic.exists(ThisWorkbook.Sheets(1).Cells(i, 3).Value) Then
        dqdic.Add ThisWorkbook.Sheets(1).Cells(i, 3).Value, ThisWorkbook.Sheets(1).Cells(i, 3).Value
    End If
Next i
dqname = dqdic.items
dqnum = dqdic.Count
For p = 2 To thiswbrow
    For i = 3 To crjcxWbrow - 1
        If crjcxWb.Sheets(1).Cells(i, 10) = ThisWorkbook.Sheets(1).Cells(p, 2) Then
            crjcxWb.Sheets(1).Cells(i, 14) = ThisWorkbook.Sheets(1).Cells(p, 3)
        End If
    Next i
Next p
Dim crjcxsheetname(35)
Dim datetime(35)
For i = 0 To daynum - 1
    crjcxyearmonth = Left(daystr(i), Len(daystr(i)) - 2)
    crjcxyear = Left(crjcxyearmonth, Len(crjcxyearmonth) - 2)
    crjcxmonth = Right(crjcxyearmonth, 2)
    crjcxday = Right(daystr(i), 2)
    dtime = crjcxyear & "/" & crjcxmonth & "/" & crjcxday
    datetime(i) = dtime
    dateyear = crjcxyear
    datemonth = crjcxmonth
Next i
FileCopy ThisWorkbook.Path & "/模板.xls", ThisWorkbook.Path & "\ADSASDSADSA" & qujian & ".xls"
Dim bzjpzWb As Workbook
bzjpz = ThisWorkbook.Path & "\DASDAASASDA" & qujian & ".xls"
Set bzjpzWb = GetObject(bzjpz)
b = 1
For i = 1 To daynum
    l = -1
    HEJI1 = 0
    HEJI2 = 0
    HEJI3 = 0
    For k = 3 To crjcxWbrow - 1
        If crjcxWb.Sheets(1).Cells(k, 5) = daystr(i - 1) And crjcxWb.Sheets(1).Cells(k, 12) = "EEE" And crjcxWb.Sheets(1).Cells(k, 14) = "DD" Then
            HEJI1 = HEJI1 + crjcxWb.Sheets(1).Cells(k, 8).Value
        End If
    Next k
    For k = 3 To crjcxWbrow - 1
        If crjcxWb.Sheets(1).Cells(k, 5) = daystr(i - 1) And crjcxWb.Sheets(1).Cells(k, 14) = "DD" And InStr(crjcxWb.Sheets(1).Cells(k, 12), "A1") > 0 Then
            HEJI1 = HEJI1 + crjcxWb.Sheets(1).Cells(k, 8).Value
        End If
    Next k
    If HEJI1 <> 0 Then
        l = l + 1
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        l = l + 1
        HEJI2 = HEJI1 / 1.06
        HEJI2 = Round(HEJI2, 2)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 3) = datemonth
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        l = l + 1
        HEJI3 = HEJI1 - HEJI2
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
    End If
    b = b + 1
Next i

b = b + 1
For i = 1 To daynum
    l = -1
    HEJI = 0
    For k = 3 To crjcxWbrow - 1
        If crjcxWb.Sheets(1).Cells(k, 5) = daystr(i - 1) And crjcxWb.Sheets(1).Cells(k, 12) = "CCC" And crjcxWb.Sheets(1).Cells(k, 14) = "DD" Then
            HEJI = HEJI + crjcxWb.Sheets(1).Cells(k, 8).Value
        End If
    Next k
    If HEJI <> 0 Then
        l = l + 1
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 3) = datemonth
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        l = l + 1
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 3) = datemonth

    End If
    b = b + 1
Next i

b = b + 1
For i = 1 To daynum
    l = -1
    HEJI = 0
    For k = 3 To crjcxWbrow - 1
        If crjcxWb.Sheets(1).Cells(k, 5) = daystr(i - 1) And crjcxWb.Sheets(1).Cells(k, 12) = "AAA" And crjcxWb.Sheets(1).Cells(k, 14) = "DL" And crjcxWb.Sheets(1).Cells(k, 7) = "BB" Then
            HEJI = HEJI + crjcxWb.Sheets(1).Cells(k, 8).Value
        End If
    Next k
    If HEJI <> 0 Then
        l = l + 1
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        l = l + 1
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 3) = datemonth

    End If
    HEJI = 0
    For k = 3 To crjcxWbrow - 1
        If crjcxWb.Sheets(1).Cells(k, 5) = daystr(i - 1) And crjcxWb.Sheets(1).Cells(k, 12) = "AAA" And crjcxWb.Sheets(1).Cells(k, 14) = "DL" And crjcxWb.Sheets(1).Cells(k, 7) = "AA" Then
            HEJI = HEJI + crjcxWb.Sheets(1).Cells(k, 8).Value
        End If
    Next k
    If HEJI <> 0 Then
        l = l + 1
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 3) = datemonth
        bzjpzWbrow = bzjpzWb.Sheets(1).Range("a65536").End(xlUp).Row
        l = l + 1
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 1) = datetime(i - 1)
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 2) = dateyear
        bzjpzWb.Sheets(1).Cells(bzjpzWbrow + 1, 3) = datemonth
    End If
    b = b + 1
Next i
Application.Windows(crjcxWb.Name).Visible = True
crjcxWb.Close True
Set crjcxWb = Nothing
Application.Windows(bzjpzWb.Name).Visible = True
bzjpzWb.Close True
Set bzjpzWb = Nothing
Application.ScreenUpdating = True
End Sub