Looking for code to update cell references, with increasing values, in large numbers of rows

[nooks12]

Hi. Recently, our IT changed our drive paths at work. I have many excel files that reference data in other files. I have a bunch of files each with several sheets where each sheet has several columns that have references for all columns. See below for sample column reference. You'll see that each new row has a filename that includes a date, incremented by one. I need to update each row by one day from early Feb to end of Jan following year. Is there a simple block of code that will do that for me. I do not have a coding background, but want to avoid days of updating each of these rows in all of these files. Thank you.

column A
row 1 file path string + filename_2023-02-06
row 2 file path string + filename_2023-02-07
row 3 file path string + filename_2023-02-08

Cheryl

 

[Candyman8019]

You could try something like the following. Of course, ensure your sheet reference and column reference are correct.

Private Sub incrementfilename()
Dim mystr As String
Dim datetext As String
Dim i As Integer

For i = 5 To 7
  mystr = Sheet17.Range("O" & i).Value
  datetext = Split(mystr, "_")(1)
  Sheet17.Range("O" & i).Value = Left(Sheet17.Range("O" & i).Value, Len(Sheet17.Range("O" & i).Value) - Len(datetext)) & Format(CDate(datetext) + 1, "YYYY-MM-DD")
Next i
End Sub

And change the loop to include the rows you want incremented, ie from '5 to 7' to '2 to 270' for example.

 

[nooks12]

Thank you. So I changed yours a bit to this:

[COLOR=rgb(44, 130, 201)]Sub incrementfilename()
Dim mystr As String
Dim datetext As String
Dim i As Integer

For i = 2 To 6
  mystr = Sheet1.Range("B" & i).Value
  datetext = Split(mystr, "_")(1)
  Sheet1.Range("B" & i).Value = Left(Sheet1.Range("B" & i).Value, Len(Sheet1.Range("B" & i).Value) - Len(datetext)) & Format(CDate(datetext) + 1, "YYYY-MM-DD")
Next i
End Sub[/COLOR]

When I run it, I get the following error message: "run-time error '9': Subscript out of range", for the datetext = Split(mystr, "_")(1)

the rows I want to change are:
=IF(ISERR('Filepath(filename-2023-02-07'!$C$7),"",'Filepath(filename-2023-02-07'!$C$7)
=IF(ISERR('Filepath(filename-2023-02-07'!$C$7),"",'Filepath(filename-2023-02-07'!$C$7)
=IF(ISERR('Filepath(filename-2023-02-07'!$C$7),"",'Filepath(filename-2023-02-07'!$C$7)
=IF(ISERR('Filepath(filename-2023-02-07'!$C$7),"",'Filepath(filename-2023-02-07'!$C$7)

I want to change them to:
=IF(ISERR('Filepath(filename-2023-02-07'!$C$7),"",'Filepath(filename-2023-02-07'!$C$7)
=IF(ISERR('Filepath(filename-2023-02-08'!$C$7),"",'Filepath(filename-2023-02-08'!$C$7)
=IF(ISERR('Filepath(filename-2023-02-09'!$C$7),"",'Filepath(filename-2023-02-09'!$C$7)
=IF(ISERR('Filepath(filename-2023-02-10'!$C$7),"",'Filepath(filename-2023-02-10'!$C$7)

And, this will be converting enough rows to cover a full year, so the months will change too.

I was hoping I could use some looping version of "Selection.Replace What" and "Replacement". Not sure if one can use looping with this? I want to highlight whole columns at a time, and just change them.

I literally have to change 365 x 3 x 3 cells in one workbook, and three workbooks altogether. Don't want to have to do something manually. Grr.

Thanks,

Cheryl

 

[Candyman8019]

It looks like you’re changing four identical rows each to something different. That could be a challenge It may help if you can share your workbook so we can see what we’re dealing with.

 

[johnnyL]

Can you provide a small XL2BB example of your sheet with the formulas that need to be changed?

 

[nooks12]

It looks like you’re changing four identical rows each to something different. That could be a challenge It may help if you can share your workbook so we can see what we’re dealing with.

Hi. Yes, that's why I was hoping for an iterative process.

I would have to share a mock workbook as my actual work workbook has protected information. I'll share Monday since I have kids and they need my attention this weekend.

 

[nooks12]

Can you provide a small XL2BB example of your sheet with the formulas that need to be changed?

I just provided the formulas above. The formulas themselves don't need to be changed, but some of the text in it, iteratively. Early next week I'll see about loading up a mock document if that helps.
Thanks

 

[johnnyL]

All you need to provide right now is a sheet that shows the column of formulas so we can see if there are any blanks in the formula column, are all of the dates going to start out the same, etc. We just need a sample of the formula column so we can see what problems might need to be addressed.

 

[johnnyL]

If you are no longer interested, please let us know so I can delete the code I have prepared awaiting for your response of formulas in the column layout.