Dear Members,
I have a Scenario where in the Sheet 1 columns (N, O, P, Q, R) have formulas.
How do i create a VBA script which will help copy all the formualas to each row..
There are only 18 rows, but in future it may grow to lakhs..
SO how do we convert this formula to VBA code & then Paste special it.. so that after execution the Formulas will not be seen.. Only values... Please suggest.
Attaching the sample file where Cloumns(N, O, P, Q, R) needs to be automated
Below is the Sample code recorded macro
Sub Macro1()
'
' Macro1 Macro
'
'
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-12],RC[-11],RC[-10],RC[-9],RC[-7],RC[-3],RC[-2],RC[-1])"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-9],RC[-7])"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7],RC[-6])"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Sheet2!C1:C4,4,0)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Sheet2!C1:C4,2,0)"
Range("N2:R2").Select
Selection.AutoFill Destination:=Range("N2:R18")
Range("N2:R18").Select
Range("P4").Select
End Sub
Bookmarks