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

June 25, 2016 at 11:29:33
Specs: Windows 7
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.


See More: How to add $ sign automatically in the formula in MS Excel

Report •

✔ Best Answer
June 30, 2016 at 14:35:22
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/



#1
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


Report •

#2
June 26, 2016 at 16:59:23
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.


Report •

#3
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/


Report •

Related Solutions

#4
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


Report •

#5
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/


Report •

#6
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.


Report •

#7
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.


Report •

#8
June 30, 2016 at 09:54:18
Hi Derby

That would work perfectly also 😊


Report •

#9
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/


Report •

#10
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/


Report •

#11
June 30, 2016 at 14:00:13
That Mike, is the badger, nicely done mate

Report •

#12
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/


Report •

#13
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.


Report •

#14
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.


Report •

Ask Question