Hey all! I'm looking for a code I can run in VBA that can look through a worksheet for a specific word and, if found, replace the formulas for the 3 cells immediately to the left of it. For Example:
I want it to look through column I:I and everytime it sees the word "Total" I want it to replace the contents of the cells in the same row for columns F,G, & H to these formulas respectively:
F:
G:
H:
Any ideas? Thanks in advance!
I want it to look through column I:I and everytime it sees the word "Total" I want it to replace the contents of the cells in the same row for columns F,G, & H to these formulas respectively:
F:
Code:
=IF(E18="Cost Per Stop",ROUND(SUMIF(P:P,"sum1",F:F)/SUMIF(S:S,"stop",F:F),2),IF(E18="Cost Per Directory",ROUND(SUMIF(P:P,"sum1",F:F)/SUMIF(S:S,"Book",F:F),2),IF(E18="Margin Percent (directs)",ROUND(F16/SUMIF(P:P,"sum1",F:F),2),IF(E18="Margin Percent (Sales)",-ROUND(F17/F14,2),IF(E18="Gross Margin",-((SUMIF(P:P,"sum1",F:F))+G15),IF(E18="total directs",SUMIF(P:P,"Sum"&N17,F:F),SUMIF(R:R,"Total"&Q16,F:F)))))))
G:
Code:
=IF(E18="Cost Per Stop",ROUND(SUMIF(P:P,"sum1",G:G)/SUMIF(S:S,"stop",G:G),2),IF(E18="Cost Per Directory",ROUND(SUMIF(P:P,"sum1",G:G)/SUMIF(S:S,"Book",G:G),2),IF(E18="Margin Percent (directs)",ROUND(G16/SUMIF(P:P,"sum1",G:G),2),IF(E18="Margin Percent (Sales)",-ROUND(G17/G14,2),IF(E18="Gross Margin",-((SUMIF(P:P,"sum1",G:G))+G15),IF(E18="total directs",SUMIF(P:P,"Sum"&N17,G:G),SUMIF(R:R,"Total"&Q16,G:G)))))))
H:
Code:
=IF(E18="Cost Per Stop",ROUND(SUMIF(P:P,"sum1",H:H)/SUMIF(S:S,"stop",H:H),2),IF(E18="Cost Per Directory",ROUND(SUMIF(P:P,"sum1",H:H)/SUMIF(S:S,"Book",H:H),2),IF(E18="Margin Percent (directs)",ROUND(H16/SUMIF(P:P,"sum1",H:H),2),IF(E18="Margin Percent (Sales)",-ROUND(H17/H14,2),IF(E18="Gross Margin",-((SUMIF(P:P,"sum1",H:H))+H15),IF(E18="total directs",SUMIF(P:P,"Sum"&N17,H:H),SUMIF(R:R,"Total"&Q16,H:H)))))))
Any ideas? Thanks in advance!