Solved How to add $ sign automatically in the formula in MS Excel

mmcconaghy June 25, 2016 at 12:59:02

Is there a specific need to 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: =$M2 in Row 3 column M This will anchor the formula to the Column, but not the Row. You can now select both cells 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+L10 Otherwise I believe you will need a macro to accomplish what you want. MIKE http://www.skeptic.com/ 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.

mmcconaghy June 27, 2016 at 07:58:00

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 http://www.skeptic.com/

AlwaysWillingToLearn June 30, 2016 at 07:48:40

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

mmcconaghy June 30, 2016 at 08:08:27

AWTL, What he is looking for is to anchor both the Column & the Row, something like: =$M$2 I believe your solution only anchors the column, so it would need to be something like: Bcell.Formula = "=$M" & "$" & Bcell.Row - 1 If that makes any sense. MIKE http://www.skeptic.com/

AlwaysWillingToLearn June 30, 2016 at 08:34:31

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

DerbyDad03 June 30, 2016 at 08:51:02

Or (I think) Bcell.Formula = "=$M$" & Bcell.Row - 1 One less Cancatenation operation for Excel to perform. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

AlwaysWillingToLearn June 30, 2016 at 09:54:18

Hi Derby That would work perfectly also 😊

mmcconaghy June 30, 2016 at 10:49:33

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$2 So 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 Sub Also 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 http://www.skeptic.com/

mmcconaghy June 30, 2016 at 12:22:40

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 Sub MIKE http://www.skeptic.com/

AlwaysWillingToLearn June 30, 2016 at 14:00:13

That Mike, is the badger, nicely done mate

mmcconaghy June 30, 2016 at 14:35:22

✔ 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 Sub With 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 http://www.skeptic.com/

AlwaysWillingToLearn June 30, 2016 at 23:49:12

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.

DerbyDad03 July 1, 2016 at 09:11:24

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.