VBA Range.Replace not working

user_2365743 0
Viewed 1140

The selection is an excel worksheet cell containing the following formula: 

=+'[FORECAST MARZO RESTRUCTURING ITALY 20170403 V3.xlsx]DETTAGLIO'!$BA$20

I run the following VBA code:

Selection.Replace what:="[FORECAST MARZO RESTRUCTURING ITALY 20170403 V3.xlsx]", _
Replacement:="[AQ_0617 - solo nuova 272.xlsx]" _
   , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
  :=False, ReplaceFormat:=False

After executing the code I expected the formula to become:

=+'[AQ_0617 - solo nuova 272.xlsx]DETTAGLIO'!$BA$20

But intead nothing happens. Any clues?

vba excel
1 Answers

The replace method doesn't work because the 2 excel files don't have the same sheets, i.e. the sheet '[AQ_0617 - solo nuova 272.xlsx]DETTAGLIO' doesn't exist. So if the replace method was executed the final formula would contain an invalid reference

user_23657430
Related