VBA: Vlookup & Filling vlookup down

[unknownymous]

Hi Guys,

I'm trying to work on a macro but I'm just recording it and here's what I got:

Sub Macro1()


ActiveCell.FormulaR1C1 = "=VLOOKUP(C1,PvA!C2:C17,16,0)"
Range("AB5").Select
Selection.Copy
Application.Goto Reference:="R10000C28"
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("C1").Select
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
End Sub


Can you help me simplify the codes. As for filling down the formula, it should be based on the last non-empty cell (I just set as 10000).

Any thoughts will be much appreciated.

 

[unknownymous]

Kindy use this macro recording instead:

Sub Macro1()
'
' Macro1 Macro
'


'
ActiveCell.FormulaR1C1 = "=VLOOKUP(C1,Sheet1!C2:C17,16,0)"
Range("AA5").Select
Selection.Copy
Application.Goto Reference:="R10000C27"
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("AA9999").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("Z1342").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Natixis Group"
Range("Z1343").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Range("A1").Select
Application.Goto Reference:="Macro1"
End Sub

Thank you

 

[taigovinda]

Hi, if this doesn't do what you want or easy for you to adapt, you should try describing in words exactly what you are trying to do, how you know which one is the bottom row, etc.

Option Explicit
Sub Macro1()
'put in a vlookup, copy it down to the bottom ("bottom" based on column z)...
'hard-code vlookup results...
'hard-code a text string overwriting bottom-most value in column Z
'put vlookup into AA5... not sure if this is the right formula for AA5 or not, may need adjustment
With Range("AA5") 'would be good to qualify this.  eg, sheet1.range("aa5")
    .Formula = "=iferror(VLOOKUP(C1,Sheet1!C2:C17,16,0),"""")"  'wrapped your formula with iferror() in order to avoid find/replace later
    .Copy
End With
'copy vlookup from AA5 all the way down to the last row that is filled ONE COLUMN OVER, IN ROW Z... then overwrite formulas with values
With Range("AA5:AA" & Range("Z" & Sheets(1).Rows.Count).End(xlUp).Row) 'z is a proxy for the column that you know will always be filled... not sure which column this is in reality
    .PasteSpecial xlPasteAll
    .Copy
    .PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
'overwrite the bottom-most filled cell in column Z with this text
Range("Z" & Sheets(1).Rows.Count).End(xlUp).Value = "Natixis Group"
End Sub

Tai