Find and Replace using VLOOKUP function

[Nidhi_]

Hello,

A range of cells in my workbook are linked to another workbook. I want to update this link (which is nested in an IF function) every month from E.g. update from

'I:\HSD\SD\PE\SA\IY\IYM 2014-15\IYM from SA\Social Assistance\DECEMBER\[SASSA 2014-15 IYM DECEMBER 2014.xlsm

to

''I:\HSD\SD\PE\SA\IY\IYM 2014-15\IYM from SA\Social Assistance\JANUARY\[SASSA 2014-15 IYM JANUARY 2014.xlsm

Basically, change 'DECEMBER' to 'JANUARY' to 'FEBRUARY' so on and so forth as each month goes by.

Now I can run a basic Find and replace function macro to automatically update from a given month to the enxt, but to automate this process I would like to run a macro which performs the function as below which replaces the data link with the name of the previous month with that of the current month:


' Macro3 Macro
'


'
Range("DS7:ED19").Select
Range("ED7").Activate
Selection.Replace What:="=VLOOKUP(MonthNo.-1,Dashboard!S3:T14,2,FALSE)", Replacement:="=VLOOKUP(MonthNo.,Dashboard!S3:T14,2,FALSE)", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


This doesn't work. Could someone please help me understand how to use the VLOOKUP function with the Find and replace function in VBA?

Thank you!
Regards,
Nidhi

 

[etaf]

i dont know how to be in VBA, so this may not help - but you could use an indirect() to pickup the month
depends on how that month changes and how you want to generate
you could simply type the month into a cell and it would update all the formulas
something like
indirect( ''I:\HSD\SD\PE\SA\IY\IYM 2014-15\IYM from SA\Social Assistance\"&CELL WITH MONTH IN&"\[SASSA 2014-15 IYM JANUARY 2014.xlsm"