Drag Down Forumula's Macro

[LionelHutz]

Hi,

So I have a macro code that looks like this:

Sub DragDown123()
'
' DragDown123 Macro
'

'
Sheets("Sheet2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!C1:C2,2,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B5")
Range("B2:B4").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("A2").Select
Sheets("Sheet1").Select
End Sub

So this simple macro is apart of a bigger macro that does some stuff and pull a formula down. THe issue is the formula only pulls down to row 5. So if I have data in a6 and on the formula does not drag that far down. For the highlighted green part of my code, is there a formula I can use instead that would auto detect how many rows down column A goes and then drag it down?

EDIT: The above sort of reads funny to me so I will try again...

Is there a way I can get the autofill to auto detect how far it needs to be dragged down based on how many rows in column A have data?

 

[shg]

Sub DragDown123()
  With Worksheets("Sheet2")
    .Range("A1").Value = "Name"
    .Range("B2", Cells(Cells(.Rows.Count, "A").End(xlUp).Row, "B")).FormulaR1C1 = _
    "=vlookup(rc[-1], Sheet1!C1:C2, 2, false)"
  End With
End Sub

 

[LionelHutz]

Sub DragDown123()
  With Worksheets("Sheet2")
    .Range("A1").Value = "Name"
    .Range("B2", Cells(Cells(.Rows.Count, "A").End(xlUp).Row, "B")).FormulaR1C1 = _
    "=vlookup(rc[-1], Sheet1!C1:C2, 2, false)"
  End With
End Sub

Awesome! So I duplicated your code to drag down two rows. Is this how to do it or is there a way I can write it to drag down multiple columns?

Sheets("Sheet2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!C1:C2,2,FALSE)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!C1:C3,3,FALSE)"
Range("B3").Select
With Worksheets("Sheet2")
.Range("A1").Value = "Name"
.Range("B2", Cells(Cells(.Rows.Count, "A").End(xlUp).Row, "B")).FormulaR1C1 = _
"=vlookup(rc[-1], Sheet1!C1:C2, 2, false)"
End With
With Worksheets("Sheet2")
.Range("A1").Value = "Name"
.Range("C2", Cells(Cells(.Rows.Count, "A").End(xlUp).Row, "C")).FormulaR1C1 = _
"=vlookup(rc[-2], Sheet1!C1:C3, 3, false)"
End With
Range("B2:B4").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("A2").Select
Sheets("Sheet1").Select
End Sub

 

[shg]

Your first post contained a good explanation of what you were trying to do. This one, not so much.

 

[LionelHutz]

Your first post contained a good explanation of what you were trying to do. This one, not so much.

Is there a formula to drag multiple columns or do I just duplicate the code like this:

With Worksheets("Sheet2")
.Range("A1").Value = "Name"
.Range("B2", Cells(Cells(.Rows.Count, "A").End(xlUp).Row, "B")).FormulaR1C1 = _
"=vlookup(rc[-1], Sheet1!C1:C2, 2, false)"
End With
With Worksheets("Sheet2")
.Range("A1").Value = "Name"
.Range("C2", Cells(Cells(.Rows.Count, "A").End(xlUp).Row, "C")).FormulaR1C1 = _
"=vlookup(rc[-2], Sheet1!C1:C3, 3, false)"
End With

 

[shg]

Sub DragDown123()
  With Worksheets("Sheet2")
    .Range("A1").Value = "Name"
    .Range("B2", .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "B")).Resize(, 2).FormulaR1C1 = _
    "=vlookup(rc[-1], Sheet1!C1:C, column(c), false)"
  End With
End Sub