CODEWITHSUNDEEP
excelvbavlookup
Germán Sanabria
Germán Sanabria

Reputation: 1

How to put a correct vlookup range(other sheet) in vba?

I´m trying this code in VBA but in the vlookup part, VBA doesn´t bring anything. "Pareo Cecos" is another sheet from the workbook.

How to put a correct vlookup range(other sheet) in VBA?

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

Upvotes: 0

Views: 38

Answers (1)

Variatus
Variatus

Reputation: 14383

I have written working code between copied lines of your original code as well as some comments. In effect, the code will work as it is (or so I hope) but it will look better if you delete the lines that are there for demonstration only.

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

Upvotes: 0

Related Questions