欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

vba 提取数据

程序员文章站 2022-05-16 09:29:24
...
Sub m_loss()
 
 
Dim d_m, r, r_loss As Integer
 
Dim date_new As Date
r = Range("a65536").End(xlUp).Row
r_loss = Sheets("LOSS").Range("a65536").End(xlUp).Row
 
zyname = InputBox("請輸入廠名")
d_m = Int(InputBox("請輸入月份date_m")) '注意數據轉換  2003,2007 好像版本不一樣
Sheets("LOSS").Cells(r_loss + 4, 1) = zyname & "的" & d_m & "月清金損耗"
Sheets("LOSS").Cells(r_loss + 4, 1).Interior.ColorIndex = 4
Sheets("LOSS").Cells(r_loss + 4, 1).Font.Bold = True
 
j = 5
For i = r To 1 Step -1
   If Month(Cells(i, 3)) = d_m And Cells(i, 1).Value = "MX" Then
            Sheets("LOSS").Cells(r_loss + j, 1).Value = Cells(i, 1)  '
            Sheets("LOSS").Cells(r_loss + j, 2).Value = Cells(i, 2)  '
            Sheets("LOSS").Cells(r_loss + j, 3) = Cells(i, 3)         '日期
            Sheets("LOSS").Cells(r_loss + j, 4).Value = Cells(i, 11)      ' 金質
            Sheets("LOSS").Cells(r_loss + j, 5).Value = Cells(i, 5)        ' 重量
            Sheets("LOSS").Cells(r_loss + j, 6).Value = Cells(i, 10)       '   類別
            Sheets("LOSS").Cells(r_loss + j, 7).Value = Cells(i, 7)        '  理論值
            Sheets("LOSS").Cells(r_loss + j, 8).Value = Cells(i + 1, 12)       '   實際回收值
            j = j + 1
     End If
      
   If Month(Cells(i, 3)) < d_m Or Year(Cells(i, 3)) < Year(Cells(r, 3)) Then       '跨年或跨月就退出
    
    
     Exit For
      
     End If
      
Next
         
         Sheets("LOSS").Cells(r_loss + j, 6) = "合計"
         Sheets("LOSS").Cells(r_loss + j, 6).Font.Bold = True
         Sheets("LOSS").Cells(r_loss + j, 6).Interior.ColorIndex = 6
 
End Sub

 

相关标签: vba