Updating formula to last row

[JoeRooney]

HI,


I have the below code and I want to drag the formula down tothe last row, that part is working fine.

I have tried two different ways of doing this but are givingthe same result.

The problem is the code is dragging the formula for "B4:AEN4"the whole way.

I need to the formula to update on each row, i.e. “B5:AEN5”then “B6:AEN6” and so on.

Any help is greatly appreciated.

Thanks

Dim lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).row[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]
Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "ISI File"
  
  Range("A4").Select
  
'  ActiveCell.FormulaR1C1 = Join(Application.Index(Range("B4:AEN4").Value, 1, 0), "|")
'  Range("A4").AutoFill Destination:=Range("A4:A" & lastrow)[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    Range("A4:A" & lastrow).FormulaR1C1 = Join(Application.Index(Range("B4:AEN4").Value, 1, 0), "|")
[/COLOR][/SIZE][/FONT]

 

[James006]

Hello,

You seem to have tested the following:

Range("A4").AutoFill Destination:=Range("A4:A" & lastrow)

Did you get your expected result ?

 

[JoeRooney]

Hi,


No I have tested both

Range("A4:A" &lastrow).FormulaR1C1 = Join(Application.Index(Range("B4:AEN4").Value,1, 0), "|")


ActiveCell.FormulaR1C1= Join(Application.Index(Range("B4:AEN4").Value, 1, 0),"|")
Range("A4").AutoFillDestination:=Range("A4:A" & lastrow)


But both give the same result for every row.

 

[James006]

Hi,

When using

Activecell.FormulaR1C1

You need to use relative references both for R and C (rows and columns)

Are you using your macro recorder ?

 

[JoeRooney]

Hi ,

No I found the code online and it is doing what I need in joining all the columns in row 4 together, My issue the code is not dragging down properly for all rows.

It give the row 4 result for all rows.

Can you advise how I can do this , I am not sure what formula replicates "FormulaR1C1 = Join(Application.Index(Range("B4:AEN4").Value, 1, 0), "|")" in excel.

 

[James006]

Hi again,

Is the initial cell where you are adding your formula : A4 ... ?

Have a test with following :

Sub InsertFormula()
Dim i As Long
Dim lastrow As Long
' Adjust to your needs
lastrow = 20
    For i = 4 To lastrow
        Range("A" & i).Formula = Join(Application.Index(Range("B" & i & ":AEN" & i).Value, 1, 0), "|")
    Next i
End Sub

Hope this will help

 

[JoeRooney]

Hi ,

Yes , so I insert column and then join al cells from B4 to AEN4 into cell A4

 

[James006]

Great ... just amended previous post with a macro to be tested ... :wink:

 

[JoeRooney]

Perfect thanks for your help. I just updated the code so I didn't have to declare the row number.

Thanks Again

Sub InsertFormula()
Dim i As Long
Dim lastrow As Long
lastrow = Range("B" & Rows.Count).End(xlUp).row

Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "ISI File"
    For i = 4 To lastrow
        Range("A" & i).Formula = Join(Application.Index(Range("B" & i & ":AEN" & i).Value, 1, 0), "|")
    Next i
End Sub

 

[James006]

You are welcome ...