VBA Loop through all worksheets in a file , find a specific value and update the cell to the right

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
44
Hi All,

I have a work book with financial year ending data in all worksheets.

I am looking for VBA code which will loop through all worksheets and does the following :

1) Find the cell containing the term :Jun-17. If Jun17 exists, name the cell to the immediate right as : FY16/17. If the term Jun17 does not exist then,
2) Find the cell containing the term :Jun-18. If Jun18 exists, name the cell to the immediate right as : FY17/18. If the term Jun18 does not exist then,
3)Find the cell containing the term :Jun-19. If Jun19 exists, name the cell to the immediate right as : FY18/19. If the term Jun19 does not exist then,
3)Find the cell containing the term :Jun-20. If Jun20 exists, name the cell to the immediate right as : FY19/20. If the term Jun20 does not exist then,
4)Find the cell containing the term :Jun-21. If Jun21 exists, name the cell to the immediate right as : FY20/21. If the term Jun21 does not exist then,
5)Find the cell containing the term :Jun-22. If Jun22 exists, name the cell to the immediate right as : FY21/22. If the term Jun22 does not exist then, stop the search and end of vba code.




Regards
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
Are the "terms" in one specific column in each sheet or can they be anywhere on each sheet? Are the "terms" formatted as 'Date' ? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Hi to all. I too was just about to ask if:
- Would the term Jun-17 be a date formatted mmm-yy ?
- Do you know that a cell can't be named FY16/17 since / caracter isn't permitted in names ?
- If the term can be found in different sheets do you know that you can't create same names.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,922
Messages
5,639,018
Members
417,062
Latest member
CM214

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