Hi, I have an excel worksheet containing column A to M with data from row 2 onwards.

E.g. For row 2, value in M2=K2+L2 and row3, value in M3=$M$2

For row4, value in M4=K4+L4 and row5, value in M5=$M$4. This repeats for rest of the rows.May I know if there is a shortcut to add $ sign for the formula in column M, instead of manually keying in the sign for each alternate row (e.g $M$2 and $M$4, etc...)

Thanks.

✔ Best Answer

Thank AWTL, here is one more that I think might be easier to use. Sub InsertFormula() Dim Bcell As Range For Each Bcell In Selection If Bcell.Row > 1 Then Bcell.Formula = "=K" & Bcell.Row & "+L" & Bcell.Row If CDbl((Bcell.Row / 2)) <> Round(Bcell.Row / 2) Then Bcell.Formula = "=$M$" & Bcell.Row - 1 End If End If Next Bcell End SubWith this version you need only select your range of cells in Column M,

no need to mark the last cell in the range.

It does not check if your range is actually in column M.With VBA, I understand the concepts,

it's the syntax that always gets me. :-)MIKE

Is there a specific needto anchor both the Column and Row?If not, then you can do something like,

enter your first formula: =K2+L2 in Row 2 column M

put your second formula:=$M2in Row 3 column M

This will anchor the formula to the Column, butnotthe Row.You can now select

bothcells M2 & M3 and drag down

and you get something like:M 1) 2) =K2+L2 3) =$M2 4) =K4+L4 5) =$M4 6) =K6+L6 7) =$M6 8) =K8+L8 9) =$M8 10) =K10+L10Otherwise I believe you will need a macro to accomplish what you want.

MIKE

message edited by mmcconaghy

Hi, Based on the current worksheet, the solution worked. Thank you.

For other worksheets, as there might new columns added/removed, I would need to fixed both the column and row. May I know if you have the VBA macro codes?

Thanks.

I'm sorry but my Macro abilities are just rudimentary

and this is a bit beyond what I can do.

Hopefully some one else will jump in with a VBA solution.MIKE

I wrote the following code that automates the above solution given by Mike, but I don't quite understand what the next step you require is? For this to work Column M must have some data in the last row that is to be used so if your column data ends in row 10 then M10 must have something within it, this can be overcome as long as I know how the worksheet is laid out. Try it and let me know what is needed next

Sub InsertFormula() Dim Bcell As Range For Each Bcell In Sheet2.Range("M1", Sheet2.Range("M" & Rows.Count).End(xlUp)) If Bcell.Row > 1 Then If CDbl((Bcell.Row / 2)) <> Round(Bcell.Row / 2) Then Bcell.Formula = "=$M" & Bcell.Row - 1 End If End If Next Bcell End Sub

AWTL, What he is looking for is to anchor both the Column & the Row,

something like:=$M$2I believe your solution only anchors the column, so it would need

to be something like:Bcell.Formula = "=$M" &

"$" &Bcell.Row - 1If that makes any sense.

MIKE

Hey Mike, Thats exactly it, that should work, i'll test it tomorrow although your code looks perfect and should work fine.

Or (I think) Bcell.Formula = "=$M$" & Bcell.Row - 1One less Cancatenation operation for Excel to perform.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi Derby That would work perfectly also ðŸ˜Š

AWTL I tested out your macro and it works as advertised.

Just needs a bit of a tweek. :-)

I believe the OP wanted both formulas entered at the same time.

So the macro would need to enter: =K2+L2

then directly below, enter the: =$M$2So something like:

Sub InsertFormula() Dim Bcell As Range For Each Bcell In Sheet2.Range("M1", Sheet2.Range("M" & Rows.Count).End(xlUp)) If Bcell.Row > 1 Then Bcell.Formula = "=K" & Bcell.Row & "+L" & Bcell.Row If CDbl((Bcell.Row / 2)) <> Round(Bcell.Row / 2) Then Bcell.Formula = "=$M$" & Bcell.Row - 1 End If End If Next Bcell End SubAlso I could only get it to work on Sheet 2 of my workbook.

Is there some way to make it active on the Current Sheet,

not restricted to just sheet 2?MIKE

OK, I think I worked it out: Sub InsertFormula() Dim Bcell As Range For Each Bcell In ActiveSheet.Range("M1", ActiveSheet.Range("M" & Rows.Count).End(xlUp)) If Bcell.Row > 1 Then Bcell.Formula = "=K" & Bcell.Row & "+L" & Bcell.Row If CDbl((Bcell.Row / 2)) <> Round(Bcell.Row / 2) Then Bcell.Formula = "=$M$" & Bcell.Row - 1 End If End If Next Bcell End SubMIKE

That Mike, is the badger, nicely done mate

Thank AWTL, here is one more that I think might be easier to use. Sub InsertFormula() Dim Bcell As Range For Each Bcell In Selection If Bcell.Row > 1 Then Bcell.Formula = "=K" & Bcell.Row & "+L" & Bcell.Row If CDbl((Bcell.Row / 2)) <> Round(Bcell.Row / 2) Then Bcell.Formula = "=$M$" & Bcell.Row - 1 End If End If Next Bcell End SubWith this version you need only select your range of cells in Column M,

no need to mark the last cell in the range.

It does not check if your range is actually in column M.With VBA, I understand the concepts,

it's the syntax that always gets me. :-)MIKE

Mike, That is absolutely perfect mate well done, seeing as the OP hasn't been back im going to mark your post as best answer because it is......nicely done.

re: "With VBA, I understand the concepts, it's the syntax that always gets me. :-)"I don't understand why you find the syntax confusing. Could it be because all of these are equivalent (I think). ;-)

Bcell.Formula = "=K" & Bcell.Row & "+L" & Bcell.Row Bcell.Formula = "=SUM(K" & Bcell.Row & ",L" & Bcell.Row & ")" Bcell.FormulaR1C1 = "=RC[-2]+RC[-1]" Bcell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History