Find Text & Replace Adjacent Cell's Formula

[Trikageon]

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:

 =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:

 =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:

 =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!

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("I" & i).Value = "Total" Then
        Range("F" & i).Formula = "=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)))))))"
        Range("G" & i).Formula = "=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)))))))"
        Range("H" & i).Formula = "=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)))))))"
    End If
Next i
End Sub

 

[lenze]

Something like this!!

Sub ReplaceFormulas()
Dim LR As Long
Dim myRng As Range
Dim C As Range
Dim stAdd As String
LR = Cells(Rows.Count, "I").End(xlUp).Row
Set myRng = Range("$I$2:$I" & LR)
With myRng
  Set C = .Find("Total", LookIn:=xlValues)
  If Not C Is Nothing Then
     stAdd = C.Address
     Do
       Cells(C.Row, "F").Formula = "=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)))))))"
       'similar for "G" and "H"
       Set C = .FindNext(C)
       Do While Not C Is Nothing And stAdd <> C.Address
   End If
End With
End Sub

Note the ""'s added to your formula!
Also, what does each of these formulas do?? My guess is there is another way (better??),

lenze

 

[Trikageon]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("I" & i).Value = "Total" Then
        Range("F" & i).Formula = "=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)))))))"
        Range("G" & i).Formula = "=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)))))))"
        Range("H" & i).Formula = "=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)))))))"
    End If
Next i
End Sub

Thanks for your reply/time! This code ran without any errors, but they didn't change anything (or at least not the cells I was hoping they would). I was thinking that possibly it might help if you knew that the presence of the word total (what we're looking for) is the result of a formula...I'm not sure if that changes anything, but just fyi.

 

[VoG]

Is it just "Total" or could it be "Total widgets"?

 

[Trikageon]

Something like this!!

Sub ReplaceFormulas()
Dim LR As Long
Dim myRng As Range
Dim C As Range
Dim stAdd As String
LR = Cells(Rows.Count, "I").End(xlUp).Row
Set myRng = Range("$I$2:$I" & LR)
With myRng
  Set C = .Find("Total", LookIn:=xlValues)
  If Not C Is Nothing Then
     stAdd = C.Address
     Do
       Cells(C.Row, "F").Formula = "=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)))))))"
       'similar for "G" and "H"
       Set C = .FindNext(C)
       Do While Not C Is Nothing And stAdd <> C.Address
   End If
End With
End Sub

Note the ""'s added to your formula!
Also, what does each of these formulas do?? My guess is there is another way (better??),

lenze

Thanks for your reply/time! I'm getting a compile error "End if without Block if." To answer your question about the formulas, basically what it's doing is replacing the hard coded totals of a report I imported with the equivilant of an autosums, based on certain criteria. I'm SURE there's a better/more efficient way of doing this, but I'm more-less a novice and this is the best I've come up with. All I know is it works when I do it manually, so if I can impliment this in my macro, I'll be set!!

 

[Trikageon]

Is it just "Total" or could it be "Total widgets"?

Presently here's the code that's resulting in the display of "Total," if it helps...

=IF(AND($E18="Gross Margin", ISBLANK(F18)),"",IF(OR($E18="Sub-total",$E18="Total Directs",$E18="Cost Per Directory",$E18="Cost Per Stop",$E18="Gross Margin",LEFT($E18,6)="Margin"),"Total",IF($F18="----------------","----------------",IF($F18="****************","****************",IF($C18>1,"Box","")))))

 

[lenze]

Sorry, had a typo!!

       Set C = .FindNext(C)
       Loop While Not C Is Nothing And stAdd <> C.Address
End If

lenze

 

[Trikageon]

Sorry, had a typo!!

       Set C = .FindNext(C)
       Loop While Not C Is Nothing And stAdd <> C.Address
End If

lenze

It ran through without an error, but it didn't change anything, that I can tell. I'm not sure if you caught it in my reply to the other gentleman, but the word "Total" that it's looking for is the result of a formula

=IF(AND($E18="Gross Margin", ISBLANK(F18)),"",IF(OR($E18="Sub-total",$E18="Total Directs",$E18="Cost Per Directory",$E18="Cost Per Stop",$E18="Gross Margin",LEFT($E18,6)="Margin"),"Total",IF($F18="----------------","----------------",IF($F18="****************","****************",IF($C18>1,"Box","")))))

Not sure if that matters or not...

 

[lenze]

Set C = .Find("Total", LookIn:=xlFormulas)

lenze