Updating formula to last row

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
171
Office Version
  1. 365
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
Code:
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]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hello,

You seem to have tested the following:

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

Did you get your expected result ?
 
Upvote 0
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.

 
Upvote 0
Hi,

When using
Code:
Activecell.FormulaR1C1

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

Are you using your macro recorder ?
 
Upvote 0
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.
 
Upvote 0
Hi again,

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

Have a test with following :

Code:
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
 
Last edited:
Upvote 0
Great ... just amended previous post with a macro to be tested ... :wink:
 
Upvote 0
Perfect thanks for your help. I just updated the code so I didn't have to declare the row number.

Thanks Again

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top