Macro to increase cell ref in formula by 1

David Beattie

New Member
Joined
Sep 16, 2014
Messages
3
Hi

I am working on a weekly report in which I have a summary sheet that references several cells in in each sheet, I have a formula to pick up the cell data I need but have to manually update the ref the cell below each time I run it, can anyone help me with a macro to increase the cell ref in the formula by 1 so I can attach it to a button?

Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,713
Office Version
  1. 365
Platform
  1. Windows
What does the formula look like? Why is the increase of 1 happening?
 

David Beattie

New Member
Joined
Sep 16, 2014
Messages
3
The current formula is =SHSC!G35

The information on each sheet are performance stats for the previous week which are sent to managers
 

David Beattie

New Member
Joined
Sep 16, 2014
Messages
3

ADVERTISEMENT

Ok so whats the formula in SHSC!G35

Hi the formula is just pulling information from a cell on another sheet, there are about 50 cells that are pulled from various sheets so its just a way to click a button and it move all the refs down 1
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe like this with the cell selected

Code:
Sub a()
With ActiveCell
    .Formula = Left(.Formula, 7) & Right(.Formula, Len(.Formula) - 7) + 1
End With
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,274
Office Version
  1. 365
Platform
  1. Windows
Hi the formula is just pulling information from a cell on another sheet ..
If that is all it is doing then you could also try something like ..
Code:
With ActiveCell
  .Formula = Split(.Formula, "!")(0) & "!" & Range(Split(.Formula, "!")(1)).Offset(1).Address
End With
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,707
Messages
5,833,242
Members
430,199
Latest member
Petty queen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top