Copy Formula to last cell in active row range then copy all the way to last column - VBA

user_18235679 0
Viewed 45

I am trying to copy a formula from C2 down to the last cell in the active row range (columns A and B count) then copy that same formula to every column in the active column range (row 1 count)

IE copy formula all the way down then all the way across....

Can you dim both last row and last column in the same Subroutine?

If so, how do I do this? I tried with two subroutines and failed miserably.

Sub ImportSub2()
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("C2:C" & LastRowColumnA).Formula = "=$A2&C$1"

Call ImportSub3
End Sub

Sub ImportSub3()
Dim lastcolumn As Long
lastcolumn = Cells(Columns.Count, 1).End(xlUp).Column
Range("C2:C" & lastcolumn).Formula = "=$A2&C$1"
End Sub
vba excel
1 Answers

Yes, this can be done; your first problem above is that you are not finding the last column correctly.

VBA formulas, especially with a mix of relative and absolute references, work better with R1C1 format. This code directly writes the required formula into the cells of interest:

Sub InjectFormula()
Dim LastRow As Long, LastCol As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Range("C2"), Cells(LastRow, LastCol)).FormulaR1C1 = "=RC1&R1C"
End Sub

If you want to copy a pre-existing formula from C2, you can instead use

Range(Range("C2"), Cells(LastRow, LastCol)).FormulaR1C1 = Range("C2").FormulaR1C1
user_44754680
Related