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

[Giggs1991]

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

 

[mumps]

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]

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.