Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row; down 2 rows, enter formula
If one follows your example's instructions, rather than your example's
cell references, one way: Dim rRng As Range Set rRng = Range("I3:I" & Range("I" & Rows.Count).End(xlUp).Row) With rRng.Offset(rRng.Count + 1, 0).Resize(1, 1) .Formula = "=SUBTOTAL(109," & rRng.Address(False, False) & ")" .AutoFill .Resize(1, 3) .Formula = Application.Substitute(.Formula, "103,", "109,") End With In article , "swatsp0p" wrote: Please translate the following commands into working code. start in column I find the last row go down two more rows (leaving one blank row) enter formula =SUBTOTAL(103,I3:I*) move one cell right (col K) enter formula =SUBTOTAL(109,J2:J*) move one more cell right (col L) enter formula =SUBTOTAL(109,L2:L*) * = the last row with data above current row. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row; down 2 rows, enter formula
Oops - I had it reversed in my test, and only changed one line in my
post. Replace .Formula = Application.Substitute(.Formula, "103,", "109,") with .Formula = Application.Substitute(.Formula, "109,", "103,") In article , "swatsp0p" wrote: Thanks for the prompt reply. One problem with this is that when this macro runs, it makes the Function operator 109 in all three cells. (I did copy/paste of your command lines) I need column "I" to be 103. I tried using different combinations of operators, but all three always end up being the same (either all 103 or all 109). Is there another way to accomplish entering the other two formulas without using the Substitute function? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter a formula that will update as more rows are added? | Excel Worksheet Functions | |||
Find text and enter in new cell | Excel Discussion (Misc queries) | |||
What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
I need a formula to find rows within a date range in one column? | Excel Worksheet Functions | |||
Find, Offset, Enter Number | Excel Programming |