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