Macro to subtract 1 from current cell

[erock_excel]

Hello,

I was wondering how to create a macro to subtract 1 from the current cell selected in a spreadsheet? I have a daily summary report that I adjust everyday by subtracting 1 from different cells throughout the summary report. I've tried to record a macro but it puts the location of the current cell (i.e. C5) instead of just current cell.

Is there any way to do this?

Thanks.

 

[ExcelGuyDude]

Activecell = Activecell - 1

 

[erock_excel]

Activecell = Activecell - 1

How would you debug the following:
Sub Minus_one()
'
' Minus_one Macro
' Report summary to subtract one from current cell
'

'
ActiveCell.Offset(-2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = ActiveCell - 1
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

I get a runtime error 13 type mismatch.

Thank you!

 

[Joe4]

Why are you trying to do all that?
If you just want to subtract one from the current cell, you should just be able to use the line of code supplied by ExcelGuyDude:

Sub Minus_one()
'
' Minus_one Macro
' Report summary to subtract one from current cell
'
    Activecell = Activecell - 1

End Sub

 

[ExcelGuyDude]

The first question I would have is why are you needing to select other cells?

To answer your question, you are referencing a cell incorrectly. ActiveCell is already referencing a "range"... hence you don't need to say Range("A1")

You can skip the steps of selecting a cell, and rather just change the value of the cell two rows above.

Either way, here is how you would fix your code:

ActiveCell.Offset(-2, 0).Select
ActiveCell = ActiveCell - 1
ActiveCell.Offset(0, 1).Select

 

[erock_excel]

Why are you trying to do all that?
If you just want to subtract one from the current cell, you should just be able to use the line of code supplied by ExcelGuyDude:

Sub Minus_one()
'
' Minus_one Macro
' Report summary to subtract one from current cell
'
   Activecell = Activecell - 1

End Sub

The other code came over from the 'record macro' button. I'm fresh to using macros and didn't know it wasn't needed. The above formula does the trick. Thanks for your help. I'm really starting to enjoy the power of macros in Excel!!

 

[erock_excel]

Why are you trying to do all that?
If you just want to subtract one from the current cell, you should just be able to use the line of code supplied by ExcelGuyDude:

[COLOR=#333333]Sub Minus_one()[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]' Minus_one Macro[/COLOR]
[COLOR=#333333]' Report summary to subtract one from current cell[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]    Activecell = Activecell - 1
[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

Is there a way to display the formula in formula bar once the macro has been executed? So if the cell value is 111 and after the macro runs the formula bar shows "=111-1".

 

[ExcelGuyDude]

The code would be

Sub Minus_one()
'
' Minus_one Macro
' Report summary to subtract one from current cell
'
    Activecell = "=" & Activecell  & "- 1"

End Sub

 

[erock_excel]

The code would be

Sub Minus_one()
'
' Minus_one Macro
' Report summary to subtract one from current cell
'
    Activecell = "=" & Activecell  & "- 1"

End Sub

That worked perfect, thanks!!

 

[Kazdima]

HI I was wondering, is it possible to update the VBA to deduct not (1), but number from previous cell from the selected range of cells? For example: if I am currently in column "F", can I deduct from numbers in column "F" numbers from column "E" of selected cells?