|
本帖最后由 BrianBrian 于 2013-3-31 21:55 编辑
程序是因为如下需求编的:
1.打开工作薄"VBA.xlsm", 程序运行.
2.工作薄" VBA 筛选测试.xlsx": sheet("ZOSO") 中, 所有"J"列值为 "#N/A" 的行, copy 该行F 和 G的单元格内容到sheet ("IPES data") A列和B列下面空白单元格. 同时"C629"中的公式自动往下填充.
3. 表格按日期另存为一个新的文件. (因每天需要更新的频率太高)
新手刚上路, 还请高人多多帮忙, 不知如下程序是哪出错了. 谢谢!
Sub PRCordcontrolnew()
Dim myapp2 As Object
Dim wkb3 As Object
Dim arr, arr1(), m As Long, n As Long, o As Long, p As Long
Set myapp2 = CreateObject("Excel.Application")
Set wkb3 = myapp2.Workbooks.Open("C:\Documents and Settings\Desktop\VBA 筛选测试.xlsx") '以新的workbook object"wkb3"调用打开范本文件
wkb3.Sheets("ZOSO").Activate
arr = wkb3.Sheets("ZOSO").Range("E2:I" & Range("E1048576").End(xlUp).Row).Value
o = wkb3.Sheets("ZOSO").Range("C1048576").End(xlUp).Row
For m = 1 To UBound(arr)
If VarType(arr(m, 5)) = vbError Then
n = n + 1
ReDim Preserve arr1(1 To 5, 1 To n) '筛选出数组arr中#N/A值的元素,并归类为新的二维数组 arr1
arr1(1, n) = arr(m, 1) '取 第1维(共5个元素)的第一个元素对应的第二维所有值
arr1(2, n) = arr(m, 2) '取 第1维(共5个元素)的第二个元素对应的第二维所有值
End If
Next m
wkb3.Sheets("IPES data").Activate '把新的数组arr1取得的如上值复制到sheets("IPES")
wkb3.Sheets("IPES data").Range("A" & o).Resize(UBound(arr1, 2), UBound(arr1)) = Application.Transpose(arr1)
wkb3.Sheets("IPES data").Range("A" & o).Resize(UBound(arr1, 2), UBound(arr1)).Borders.LineStyle = 1
wkb3.Sheets("IPES data").Range("A" & o + UBound(arr1, 2)).Select
p = wkb3.Sheets("IPES data").Range("A1048576").End(xlUp).Row
With wkb3.Sheets("IPES data")
.Range("C628").AutoFill Destination:=.Range("C628:C" & p), Type:=xlFillDefault 'Sheet(IPES)单元格C628公式自动往下填充
End With
wkb3.SaveAs Filename:="C:\Documents and Settings\Desktop\PRC order control\" & "PRC Order Control" & Format(Now(), "YYYYMMDDHH") & " .xlsx" 'WK3 按日期和时间另存为新文件
wkb3.Close 0
Set wkb3 = Nothing
Set myapp2 = Nothing
End Sub
本帖最后由 hwc2ycy 于 2013-4-1 08:31 编辑
- Sub PRCordcontrolnew2()
- On Error Resume Next
- Dim wkb3 As Workbook
- Dim arr, arr1(), m As Long, n As Long, o As Long, p As Long
- Application.ScreenUpdating = False
- Set wkb3 = GetObject(ThisWorkbook.Path & "\VBA 筛选测试.xlsx")
-
- With wkb3.Sheets("ZOSO")
- arr = .Range("E2:I" & .Cells(Rows.Count, 3).End(xlUp).Row)
- End With
- For m = 1 To UBound(arr)
- If VarType(arr(m, 5)) = vbError Then
- n = n + 1
- ReDim Preserve arr1(1 To 2, 1 To n) '筛选出数组arr中#N/A值的元素,并归类为新的二维数组 arr1
- arr1(1, n) = arr(m, 1)
- arr1(2, n) = arr(m, 2)
- End If
- Next m
- With wkb3.Sheets("IPES data")
- p = .Cells(Rows.Count, 1).End(xlUp).Row
- If p > 1 Then Range("a2:c" & p) = ""
- If n > 0 Then
- .Range("A" & 2).Resize(n, UBound(arr1)) = Application.Transpose(arr1)
- .Range("A" & 2).Resize(n, UBound(arr1)).Borders.LineStyle = 1
- .[c2] = "=MID(RC[-1],11,4)/1000*MID(RC[-1],16,4)/1000*MID(RC[-1],29,4)"
- .Range("C2").AutoFill Destination:=.Range("C2:C" & n + 1), Type:=xlFillDefault 'Sheet(IPES)单元格C628公式自动往下填充
- End If
- End With
- wkb3.SaveAs Filename:="C:\Documents and Settings\Desktop\PRC order control" & "PRC Order Control" & Format(Now(), "YYYYMMDDHH") & " .xlsx" 'WK3 按日期和时间另存为新文件
- wkb3.Close 0
- Set wkb3 = Nothing
-
- Application.ScreenUpdating = True
-
- End Sub
复制代码
|
|