CODEWITHSUNDEEP
excelvbaexcel-formula
Pablo
Pablo

Reputation: 140

Modify SUMIFS formula in VBA - Ranges

I have a very big Excel file with lots of formulas. One of them refers to another file: 

    =SUMIFS('T:\3Q2018\[TI -Sept 18.xlsm]Base CM1'!$C$5:$C$59;
'T:\3Q2018\[TI -Sept 18.xlsm]Base CM1'!$A$5:$A$59;
$C16;
'T:\3Q2018\[TI -Sept 18.xlsm]Base CM1'!$R$5:$R$59;
"DOLARES")

What I want to do is to easily modify the path. I have the same formula in several cells, so if I need to change it I have to go cell by cell.

For example, the previous formula would be changed like this: 

=SUMIFS('T:\4Q2018\[TI -Dec 18.xlsm]Base CM1'!$C$15:$C$49;
'T:\4Q2018\[TI -Dec 18.xlsm]Base CM1'!$A$15:$A$49;
$C16;
'T:\4Q2018\[TI -Dec 18.xlsm]Base CM1'!$R$15:$R$49;
"DOLARES")

I have to change Sept for Dec, 3Q2018 for 4Q2018 and the range.

That's what I want to make simpler. I've tried with VBA and with another formulas, but have no success.

I've thought in writing the path in one cell and get it from there, but I don't now how.

Any help?

Upvotes: 0

Views: 129

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

Something like this:

Dim c as range, f
'loop over selected cells
for each c in selection.cells
    f = c.formula
    f = Replace(f, "-Sept", "-Dec")
    f = Replace(f, "3Q2018", "4Q2018")        
    '...replace ranges
    c.formula = f
next c

Upvotes: 1

Related Questions