Find and Replace when a File Exists

scotttiger

New Member
Joined
Jul 21, 2014
Messages
6
I want to have a find and replace macro for a spreadsheet that uses vlookup to get a file for a particular month


The vlookup function will have a network path like
VLOOKUP("% Return - Month",'C:\DATA\ Spreadsheets\[a_spreadsheet.xls]Jun 14'!$R$5:$T$37,3,0)*100



For every month I need to find and replace the current month (Jun 14) with the next month's spreadsheet (Jul 14)


but if it hasn't been created yet I would like it to be skipped over/ keep the same month.


Any website/guide/code would be great... I'm a complete newby to VB, so far I've tried to look at tutorials on how write the find and replace portion of the code


but the obstacle is knowing if a file exists or does not exist...


If my end product can follow this scheme that would be awesome... or letting me know if this is possible or not possible for Microsoft Excel 2007 :(:


in each cell of Col A do this
if (C://.../.../.../[spreadsheet.xls]Jul '14 EXISTS)
replace Jun with Jul
else
replace Jun with Jun/ skip / do nothing

Sorry for the lengthy post :( thanks for reading!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Code:
    If Len(Dir("\\someServer\somePath\someFile.xls")) Then
        ' the file exists
    End If
 

scotttiger

New Member
Joined
Jul 21, 2014
Messages
6
Code:
    If Len(Dir("\\someServer\somePath\someFile.xls")) Then
        ' the file exists
    End If


Thanks for your reply, but how would I include the month I am looking for in that spreadsheet? Since all the spreadsheets do exist... but I don't know if for a particular month if it exists or not...
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Select col A do Ctrl+G (Go to), tick Formulas, untick everything except Errors, then press OK. That selects all the cells with errors, correct?

Then you can do Find & Replace, and replace "Jul '14" with "Jun '14"

Does that do what you want, aside from not doing it with VBA?
 

scotttiger

New Member
Joined
Jul 21, 2014
Messages
6

ADVERTISEMENT

The problem is I have a lot of network paths ... when I do Find and replace and replace "Jun 14" with "Jul 14" the issue is that most of the time the sheet doesn't have the new month sheet made yet so for each of the cells the "Select Sheet" window would pop up and I have to scroll past x amount of sheets to always select the most recent one for each one... I'll post a screen shot

2djnihi.jpg

This is a growing spreadsheet too which might be a pain for the next intern that takes over... I just want things easier for them because it is a huge waste of time
 

scotttiger

New Member
Joined
Jul 21, 2014
Messages
6
Maybe I'm approaching this problem the wrong way....

How do I easily update network paths for each month when they're made while leaving the other ones alone...
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

How about instead putting the most recent value in the same place (or a workbook-level named range) in each of those workbooks?
 

scotttiger

New Member
Joined
Jul 21, 2014
Messages
6
I'm sorry shg, I'm new to excel as well... will a workbook level named range have to be updated manually? If so I don't think that's going to solve the problem :C
 

scotttiger

New Member
Joined
Jul 21, 2014
Messages
6
Will this work?

Replace Jun with Jul....

If vlookup return #N/A then

replace jul with jun?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Umm -- if that works for you, sure.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,009
Messages
5,599,326
Members
414,305
Latest member
scarletX

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