VBA help - Rollforward date within text

[DaniNaj]

Hi,

I was wondering if anyone could help me as i'm a bit stuck on the following task. I need to automate the roll-forward of an excel spreadsheet where the year which is being increased by 1 is within text.

ABC2019ABCDEFG will need to be roll-forwarded to ABC2020ABCDEFG.

The issues I'm having is that not all of the format is the same; some use the format for the year "2019" whereas others use "19". The location of the year is also not always the same within the text, e.g:

ABC2019ABCDEFG or ABCDE2019ABCDE

Also, not all of the years are 2019 - some of the columns are using last year (2018) and other's use 2020 already.

Any help/guidance would be greatly appreciated.

Thanks in advance.

 

[Peter_SSs]

Some take YYYY as the format whereas other's take YYYY.

Do you mean some are YY?

However, there are columns within the spreadsheet we don't want to rollforward ..

So can we just specify which columns should roll and/or which columns should not?
Otherwise, how do we decide whether a 2 or 4 digit number does "correspond to the year" or not?

Perhaps explaining exactly what is wrong with individual suggestions above would also help those people fine-tune or alter their suggestions to better meet your requirements.
There are also some unanswered questions above.

Remember that you are very familiar with your data and requirements but we are not so we need as much clear information as we can get to replicate what you have and develop possible solutions.