How would i go about selecting a row to the left of any active cell. As far as column B
i tried this but it selects the whole row to the leftRange(ActiveCell.End(xlToLeft))
How would i go about selecting a row to the left of any active cell. As far as column B
i tried this but it selects the whole row to the leftRange(ActiveCell.End(xlToLeft))
Try:
HTHRange(Cells(ActiveCell.Row, "B"), ActiveCell.Offset(, -1)).Select
Jason
Range(ActiveCell.offset( 0 , -1), Cells(ActiveCell.Row, 2)).Select
Shijesh Kumar
http://shijesh.wordpress.com/
Thank you
Do you know how i would use it in this code
Month Column = 14 at this point (is Variable)For Ws = 1 To 2 With Worksheets(Ws) .Cells(4, MonthColumn) = "Total" .Cells(7, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(8, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(9, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(10, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(11, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(12, MonthColumn).FormulaR1C1 = "=SUM(R)" 'Total column Sheets 1 and 2 .Cells(13, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(14, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(15, MonthColumn).FormulaR1C1 = "=SUM(R)" .Cells(16, MonthColumn).FormulaR1C1 = "=SUM(R)" End With Next Ws
Forgot to say it needs to calculate from MonthColumn variable(14) back to column(B)
And give the total
Are you trying to create the correct SUM formula? If so, this may be what you are looking for:
HTHFor Ws = 1 To 2 With Worksheets(Ws) .Cells(4, MonthColumn) = "Total" For i = 7 To 16 .Cells(i, MonthColumn).FormulaR1C1 = "=SUM(RC2:RC)" & MonthColumn - 1 Next i End With Next Ws
Jason
Im afraid the code didnt work
I need a total for columns "B" to the one before "MonthColumn"(variable)
And need rows 4 and 7-16 filled as shown in my macro
The amount of columns is dynamic rows are not. I tried xlToLeft and such but havent been successeful
George
Hi George.
Once you run the procedure I listed above, what are the formulas in the cells? What "didnt work" about the procedure?
Jason
It does add a total heading to row B4
Error 1004 Object Error on row
.Cells(i, MonthColumn).FormulaR1C1 = "=SUM(RC2:RC)" & MonthColumn - 1
When the error is presented, what is the value of MonthColumn? You can see this by clicking Debug, then hovering the cursor over the variable MonthColumn.
MonthColumn Is 12 at error
It pastes the Total heading in the correct place. which is a good sign. The rest should follow
I defined i As Integer Is this wrong may cause an object error.
George
Interesting... See if this works:
If not, is it possible you could post up a copy of your workbook?For Ws = 1 To 2 With Worksheets(Ws) .Cells(4, MonthColumn) = "Total" For i = 7 To 16 .Cells(i, MonthColumn).FormulaR1C1 = "=SUM(RC2:RC[-1])" Next i End With Next Ws
Thats Great thanx, it worked successfully and added the column after the variable column number.
I forgot to mention the average column so i just updated that and it now works.
Than you for your timeDim i As Long For Ws = 1 To 2 With Worksheets(Ws) .Cells(4, MonthColumn) = "Total" For i = 7 To 15 .Cells(i, MonthColumn).FormulaR1C1 = "=SUM(RC2:RC[-1])" .Cells(16, MonthColumn).FormulaR1C1 = "=AVERAGE(RC2:RC[-1])" Next i End With Next Ws
George
Glad it worked, George.
By the way, you should move the average formula after the loop, or it will insert the formula 9 times. (Move it between Next i and End With).
Jason
Yep i know what you mean didnt think about that
GeorgeDim i As Long For Ws = 1 To 2 With Worksheets(Ws) .Cells(4, MonthColumn) = "Total" For i = 7 To 15 .Cells(i, MonthColumn).FormulaR1C1 = "=SUM(RC2:RC[-1])" .Cells(16, MonthColumn).FormulaR1C1 = "=AVERAGE(RC2:RC[-1])" Next i .Cells(16, MonthColumn).FormulaR1C1 = "=AVERAGE(RC2:RC[-1])" End With Next Ws
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks