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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
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
47,926
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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