[求助]使用VLOOKUP出了错,各位大侠能否帮忙改改啊,谢谢!!!

stonewind

每天都处理相同的数据,想进行汇总.对多个文档进行处理时,使用VLOOKUP无法往下执行了,各位大侠看看VLOOKUP是否支持这样的写法.如果不行,我应该怎么修改啊?

ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(RC[-1],[a(i)]sheet1!R1:R65536,2,0)"

[此贴子已经被作者于2005-10-1 11:01:41编辑过]

kangking

Option ExplicitSub main()

Application.ScreenUpdating = False

'On Error Resume Next

Dim file_no As Integer Dim fs, f, f1, fc, x, y, total, yy, i, xxx, aaa, rg1, bbb, rg2    Set fs = CreateObject("Scripting.FileSystemObject")

    Set f = fs.GetFolder(ActiveWorkbook.Path)

    Set fc = f.Files

    Dim a(200) As String

  

    x = 1

    For Each f1 In fc

        If f1.Name <> "help.xls" And Right(f1.Name, 3) = "xls" Then

        a(x) = f1.Name

        x = x + 1

        End If

  

        

    Next

    file_no = x - 1

     Workbooks("help.xls").Activate

     Workbooks("help.xls").Sheets("sheet1").Range("A2:S63884").Select

    Selection.Delete Shift:=xlUp

   y = 1

  Do Until Workbooks("help.xls").Sheets("sheet3").Cells(y, 2) = ""

  y = y + 1

  Loop

  total = y

  yy = total - 1

                                                                                 

  If file_no > 0 Then

   For i = 1 To file_no

    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & a(i)

    Workbooks(a(i)).Activate

    For xxx = 1 To yy

    Workbooks("help.xls").Sheets("sheet2").Cells(xxx + 1 + yy * (i - 1), 3) = Workbooks("help.xls").Sheets("sheet3").Cells(xxx, 2)

   

   ''参数aaa bbb用于定位在哪个RANGE写数据''

   

   ''执行到这一段时就无法得到相应的结果了"

    aaa = 2 + total * (i - 1)

    rg1 = "D" & aaa

     Workbooks("help.xls").Activate

    Workbooks("help.xls").Sheets("sheet2").Select

    Workbooks("help.xls").Sheets("sheet2").Range(rg1).Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & a(i) & "]sheet1!R1:R65536,2,0)"

    bbb = 1 + total * (i - 1) + total

    rg2 = "D" & aaa & "" & bbb

   Workbooks("help.xls").Sheets("sheet2").Range(rg1).Select

   Selection.AutoFill Destination:=Workbooks("help.xls").Sheets("sheet2").Range(rg2)

  

  ''以上代码是否有方法更正啊？''

   Next xxx

    Workbooks(a(i)).Close savechanges:=False

   Next i

     



  End If

MsgBox ("OK,I hope it can help you!")

Workbooks("help.xls").SaveEnd Sub