PDA

View Full Version : Formula R1C1



Techgirl
09-28-2011, 05:07 PM
Ok, so this is an easy problem but im not exactly sure what to do..

I have an excel sheet with sales totals from 12 months and 10 different products in the range B4:M13. im supposed to write a VBA sub to enter formulas for the totals in column n and row 14, hopefullly i wasnt too hard to follow but this is a relativly simple problem im just not sure how to do :banghead: Help? :(

omp001
09-28-2011, 07:33 PM
Hi. Is this what you want?
Sub FillFormulae()

[B14] = "=SUM(B5:B13)"
[B14:M14].FillRight

[N5] = "=SUM(B5:M5)"
[N5:N13].FillDown

End Sub

Techgirl
09-28-2011, 11:51 PM
Hi. Is this what you want?
Sub FillFormulae()

[B14] = "=SUM(B5:B13)"
[B14:M14].FillRight

[N5] = "=SUM(B5:M5)"
[N5:N13].FillDown

End Sub

Wow looks great thanks!

Just curious, how would i go about coding it if i didn't know how many columns or rows i was taking data from? I'd assume id take information and store it to a variable but cant seem to work it out. Any advice is great, Thanks Guys!

Bob Phillips
09-29-2011, 01:01 AM
Assuming you have headings in row 3, try



With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
lastcol = .Cells(3, .Columns.Count).End(xlToLeft).Column
.Cells(lastrow + 1, "B").Resize(, lastcol - 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End With

mikerickson
09-29-2011, 01:54 AM
... im supposed to write a VBA sub ... not sure how to do
82% of the code that I write begins with me recording a macro.