|
mypath = "C:\Documents and Settings\Administrator\桌面\实验数据\"
myfile1 = Dir(mypath & "*商品销售统计*.xls")
Workbooks.Open mypath & myfile1
For Each i In Range("A1", Cells(3, 15)) '前三行,15列内找列名称
If i.Value = "商品名称" Then '找到“商品名称”这列
Set a = Workbooks(myfile1).Sheets(1).Cells(Range(i.Address).Row + 1, Range(i.Address).Column) '定位名称下一行
Exit For
End If
Next i
For Each p In Range("A1", Cells(3, 15)) '前三行,15列内找列名称
If p.Value = "数量" Then '找到“数量”这列
Set b = Workbooks(myfile1).Sheets(1).Cells(Range(p.Address).Row + 1, Range(p.Address).Column) '定位数量下一行
Exit For
End If
Next p
For Each e In Range(b, Cells(Rows.Count, b.Column)) '转化成数字
e.Value = e.Value + 0 '文本值 +0 可以转换成数字格式
If e.Value = 0 Then
Set t = Workbooks(myfile1).Sheets(1).Cells(Range(e.Address).Row - 1, Range(e.Address).Column)
Exit For
End If
Next e
myfile2 = Dir(mypath & "*仓库进销存变动表*.xls")
Workbooks.Open mypath & myfile2
Workbooks(myfile1).Sheets(1).Range(a.Value, Cells(t.Row, a.Column)).Copy 这句一直报错,请高手帮忙看看
Workbooks(myfile2).Sheets(1).Range(Cells(b.Row, b.Column + 14), Cells(t.Row, t.Column + 14)).PasteSpecial xlPasteValues
Workbooks(myfile1).Sheets(1).Range(b, t).Copy
Workbooks(myfile2).Sheets(1).Range(Cells(b.Row, b.Column + 15), Cells(t.Row, t.Column + 15)).PasteSpecial xlPasteValues
|
|