时间戳

首页 > 解决方案 > 如何在 vba 中放置正确的 vlookup 范围（其他工作表）？

excel - 如何在 vba 中放置正确的 vlookup 范围（其他工作表）？

问题描述

我在 VBA 中尝试这段代码，但在vlookup某种程度上，VBA 没有带来任何东西。“ Pareo Cecos ”是工作簿中的另一张纸。

如何在 VBA 中放置正确的 vlookup 范围（其他工作表）？

Sub Macro3()
' --------------
' Macro3 Macro
' --------------
Worksheets("Altas").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'Pareo Cecos'!C[1]:C[4],3,0)"
Range("M3").Select
Selection.AutoFill Destination:=Range("M3", "M" & Cells(Rows.Count, 1).End(xlUp).Row)
Range("M3", "M" & Cells(Rows.Count, 1).End(xlUp).Row).Select
Selection.Copy
Range("D3", "D" & Cells(Rows.Count, 1).End(xlUp).Row).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("M3", "M" & Cells(Rows.Count, 1).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

End Sub

标签： excelvbavlookup

解决方案

我已经在复制的原始代码行之间编写了工作代码以及一些注释。实际上，代码将按原样工作（或者我希望如此），但如果您删除仅用于演示的行，它会看起来更好。

Sub Macro3()
    ' 247

    Dim Altas       As Worksheet
    Dim Rng         As Range
    
    
    ' Worksheets("Altas").Select                        ' don't Select any thing
    Set Altas = Worksheets("Altas")
    ' the ActiveCell could be anywhere. Specify the address
    ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'Pareo Cecos'!C[1]:C[4],3,0)"
    Set Rng = Worksheets("Pareo Cecos").Range("A1:C4")  ' specify the lookup range
    
    With Altas
        ' I assume the cell to be M3
        .Cells(3, "M").Formula = "=VLOOKUP($M3, " & Rng.Address & ", 3)"
    
        'Range("M3").Select                             ' not required
        ' define the destination range (to be used thrice)
        Set Rng = .Range("M3", "M" & .Cells(Rows.Count, 1).End(xlUp).Row)
        'Selection.AutoFill Destination:=Range("M3", "M" & Cells(Rows.Count, 1).End(xlUp).Row)
        .Cells(3, "M").AutoFill Destination:=Rng
    
        'Range("M3", "M" & Cells(Rows.Count, 1).End(xlUp).Row).Select
        'Selection.Copy
        Rng.Copy
        'Range("D3", "D" & Cells(Rows.Count, 1).End(xlUp).Row).Select
        Debug.Print Rng.Offset(0, -9).Address
        'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        '    :=False, Transpose:=False
        Rng.Offset(0, -9).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                       SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        'Range("M3", "M" & Cells(Rows.Count, 1).End(xlUp).Row).Select
        'Selection.ClearContents
        Rng.ClearContents
        
        'Range("A2").Select
        Set Rng = .Range(.Cells(2, "A"), .Cells(2, .Columns.Count).End(xlToLeft))
        'Range(Selection, Selection.End(xlToRight)).Select
        'Selection.Copy
        Rng.Copy Destination:=.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        'Range(Selection, Selection.End(xlDown)).Select
        'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        '    SkipBlanks:=False, Transpose:=False
        'Rows("2:2").Select
        'Application.CutCopyMode = False
        'Selection.Delete Shift:=xlUp
    End With
End Sub

推荐阅读