Solved: Fill down formula

[satyen]

Hi my fill down formula doesn't seem to be working. Can someone please help

Range("A2,A", Range("65536").End(xlUp).Row).FormulaR1C1="RIGHT(RC[5,7)"

Thanks.

[Bob Phillips]

Range("A2,A" & Range("A65536").End(xlUp).Row).FormulaR1C1 = "=RIGHT(RC[5],7)"

[marshybid]

Hi xld,

I just tried this as it looked like a tidier solution for me to copy down formulas across ranges.

However, when I run it it only fills in the formula in cells A1 and A2??

I am always looking for ways to tidy up or simplify my code. Often, as I'm sure you can tell, I just record a macro for what I want to do and then try to edit it to match my requirements, which often means it is all in long hand.

Any suggestions?

Thanks, Marshybid :yes

[satyen]

I tried this too, and yes it only copies down to A1 and A2. Can it be changed so that it fills down until there is a value in column b please. I don't need the formula to be added to A1. many thanks.

[satyen]

I Changed it slightly but now it copies formula into A2 and A4 not A3:

Sub Test()
Range("A2").Select
Range("A2,A" & Range("B65536").End(xlUp).Row).FormulaR1C1 = "=RIGHT(RC[5],7)"
End Sub

[Bob Phillips]

Gentlemen,

it sounds as you both only have limited data in column A. The code as written will only fill-down as far as there is data in column A. If you want it to go further, you will need some other logic to determine where it goes to/stops at.

[satyen]

This seems to work now:

Sub Test()
Range("A2").FormulaR1C1 = "=RIGHT(RC[5],7)"
LastRow = Range("B65536").End(xlUp).Row 'Find the row number of the last cell used in column "B"
With Range(Cells(2, 1), Cells(LastRow, 1))
.FillDown
'.Copy
'.PasteSpecial Paste:=xlPasteValues
End With
End Sub

[marshybid]

This seems to work



Sub Test()

Range("A2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[5],7)"
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).selct
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False

End Sub


Is this what you meant?? This will create the formula in cell A2 then find last populated cell in cloumn B and copy formula to that row in clumn A

Marshybid

[Bob Phillips]

That's the idea satyen!

But you can do it in oe



Sub Test()
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("A2").Resize(LastRow - 1).FormulaR1C1 = "=RIGHT(RC[5],7)"
End Sub

[satyen]

This cuts it down even more- cheers for that!

[Alexon2008]

LastRow = Range("B65536").End(xlUp).Row
I don't understand how you find last row. Can anyone explain this?

[Bob Phillips]

Basically, it is starting at the bottom of a column, and then works its way up until it finds a cell that is not empty. It has the advantage of finding the last cell, even if there were blanks in that column.

This is the same as selecting B65536 and doing a Ctrl-Up Arrow.

It is more ribust to use

LastRow = Range("B" & Rows.Count).End(xlUp).Row