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.
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. :-)
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+L10Otherwise I believe you will need a macro to accomplish what you want.
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.
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 - 1
If that makes any sense.
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?
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
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, 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.