宏，vlookup函数

joywwd

我在excel录制了个宏，里面有个vlookup函数，一开始它还能自动运行，到后来，不知道为什么就没有了，但是vba里面有这个函数

之皓1990

上传附件 看看

joywwd

之皓1990 发表于 2013-10-11 23:48
上传附件 看看

不好意思，这段时间没登，才看见回复。

joywwd

joywwd 发表于 2013-10-20 21:56
不好意思，这段时间没登，才看见回复。

Sub Macro1()
'
' Macro1 Macro
' 宏由 王巍栋 录制，时间: 2013/10/19
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\joywwd\Desktop\开发软件\开户申请名单.txt", Destination:=Range("A1"))
        .Name = "开户申请名单"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 2, 2, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet2").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\joywwd\Desktop\开发软件\核查例1.txt", Destination:=Range("A1"))
        .Name = "核查例1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 2, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("J1").Select
    Windows("最新粘贴版.xls").Activate
    Sheets("Sheet2").Select
    Range("J1").Select
    Selection.Copy
    Windows("空表.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("J1:J518")
    Range("J1:J518").Select
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 230
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 259
    ActiveWindow.ScrollRow = 266
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 295
    ActiveWindow.ScrollRow = 327
    ActiveWindow.ScrollRow = 366
    ActiveWindow.ScrollRow = 394
    ActiveWindow.ScrollRow = 401
    ActiveWindow.ScrollRow = 406
    ActiveWindow.ScrollRow = 409
    ActiveWindow.ScrollRow = 412
    ActiveWindow.ScrollRow = 417
    ActiveWindow.ScrollRow = 421
    ActiveWindow.ScrollRow = 423
    ActiveWindow.ScrollRow = 427
    ActiveWindow.ScrollRow = 430
    ActiveWindow.ScrollRow = 433
    ActiveWindow.ScrollRow = 434
    ActiveWindow.ScrollRow = 435
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 437
    ActiveWindow.ScrollRow = 438
    ActiveWindow.ScrollRow = 439
    ActiveWindow.ScrollRow = 440
    ActiveWindow.ScrollRow = 441
    ActiveWindow.ScrollRow = 442
    ActiveWindow.ScrollRow = 443
    ActiveWindow.ScrollRow = 445
    ActiveWindow.ScrollRow = 447
    ActiveWindow.ScrollRow = 450
    ActiveWindow.ScrollRow = 455
    ActiveWindow.ScrollRow = 456
    ActiveWindow.ScrollRow = 457
    ActiveWindow.ScrollRow = 459
    ActiveWindow.ScrollRow = 463
    ActiveWindow.ScrollRow = 466
    ActiveWindow.ScrollRow = 470
    ActiveWindow.ScrollRow = 471
    ActiveWindow.ScrollRow = 474
    ActiveWindow.ScrollRow = 477
    ActiveWindow.ScrollRow = 478
    ActiveWindow.ScrollRow = 479
    ActiveWindow.ScrollRow = 480
    ActiveWindow.ScrollRow = 482
    ActiveWindow.ScrollRow = 483
    ActiveWindow.SmallScroll Down:=12
    Selection.AutoFill Destination:=Range("J1:J2170"), Type:=xlFillDefault
    Range("J1:J2170").Select
    ActiveWindow.ScrollRow = 2135
    ActiveWindow.ScrollRow = 2132
    ActiveWindow.ScrollRow = 2128
    ActiveWindow.ScrollRow = 2125
    ActiveWindow.ScrollRow = 2122
    ActiveWindow.ScrollRow = 2118
    ActiveWindow.ScrollRow = 2115
    ActiveWindow.ScrollRow = 2108
    ActiveWindow.ScrollRow = 2099
    ActiveWindow.ScrollRow = 2069
    ActiveWindow.ScrollRow = 2019
    ActiveWindow.ScrollRow = 1949
    ActiveWindow.ScrollRow = 1770
    ActiveWindow.ScrollRow = 1578
    ActiveWindow.ScrollRow = 1244
    ActiveWindow.ScrollRow = 1071
    ActiveWindow.ScrollRow = 949
    ActiveWindow.ScrollRow = 826
    ActiveWindow.ScrollRow = 770
    ActiveWindow.ScrollRow = 690
    ActiveWindow.ScrollRow = 644
    ActiveWindow.ScrollRow = 607
    ActiveWindow.ScrollRow = 578
    ActiveWindow.ScrollRow = 558
    ActiveWindow.ScrollRow = 535
    ActiveWindow.ScrollRow = 518
    ActiveWindow.ScrollRow = 511
    ActiveWindow.ScrollRow = 501
    ActiveWindow.ScrollRow = 488
    ActiveWindow.ScrollRow = 468
    ActiveWindow.ScrollRow = 402
    ActiveWindow.ScrollRow = 240
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 1
    Range("H10").Select
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=-6
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "姓名"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "性别"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "证件"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "证件号"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "联系方式"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "有效期"
    Range("I1").Select
    Windows("最新粘贴版.xls").Activate
    Sheets("Sheet1").Select
    Range("I1:S2").Select
    Selection.Copy
    Windows("空表.xls").Activate
    ActiveSheet.Paste
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],[最新粘贴版.xls]Sheet2!C8:C10,3,0)"
    Selection.AutoFill Destination:=Range("I2:I8914"), Type:=xlFillDefault
    Range("I2:I8914").Select
    ActiveWindow.ScrollRow = 8865
    ActiveWindow.ScrollRow = 8851
    ActiveWindow.ScrollRow = 8824
    ActiveWindow.ScrollRow = 8727
    ActiveWindow.ScrollRow = 8314
    ActiveWindow.ScrollRow = 6880
    ActiveWindow.ScrollRow = 5405
    ActiveWindow.ScrollRow = 4454
    ActiveWindow.ScrollRow = 4109
    ActiveWindow.ScrollRow = 3861
    ActiveWindow.ScrollRow = 3627
    ActiveWindow.ScrollRow = 3406
    ActiveWindow.ScrollRow = 3282
    ActiveWindow.ScrollRow = 3020
    ActiveWindow.ScrollRow = 2358
    ActiveWindow.ScrollRow = 1848
    ActiveWindow.ScrollRow = 1338
    ActiveWindow.ScrollRow = 1063
    ActiveWindow.ScrollRow = 594
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 1
    Range("J2:S2").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Selection.AutoFill Destination:=Range("J2:S8914")
    Range("J2:S8914").Select
    Range("R11").Select
    Columns("L:R").ColumnWidth = 0
    Columns("K:K").ColumnWidth = 0
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I8914")
    Range("I2:I8914").Select
    Columns("I:J").ColumnWidth = 0
    Columns("H:H").ColumnWidth = 8.25
    Columns("S:S").EntireColumn.AutoFit
    Range("T1:V1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "#N/A代表证件号误或未核查"
    Range("S1").Select
    With Selection.Interior
        .ColorIndex = 33
        .Pattern = xlSolid
    End With
    Range("U9").Select
    ActiveWorkbook.RemovePersonalInformation = False
End Sub

joywwd

joywwd 发表于 2013-10-20 21:58
Sub Macro1()
'
' Macro1 Macro

批量开卡的自动匹配，但是更换数据后，最后一行显示 #REF!，不知道为什么最后一行vlookup不能自动生成，请指教。
我设计了一个外部数据导入，第一个导入数据为“开户表”，第二个为“核查例”，到放到附件里了。
数据我修改了一下，因为涉及别人隐私

joywwd

导入数据，就成这样 最后一行 没有匹配  上面的可能是我修改数据的问题