Find Text & Replace Adjacent Cell's Formula

T

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
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:
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!
 
Sort by date Sort by votes

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I was in a hurry and posted that without thinking!! The original code
Code: 
Set C = .Find("Total", LookIn:=xlValues)
is correct and DOES work :)
If you can't get it to work. copy and post the EXACT code you have in your module.

lenze
 
Upvote 0
The preceding code is:

Rich (BB code): 
Sub a()
'

'Initial Setup
    Workbooks.OpenText Filename:= _
        "\\Cisora12\it-windev_dscranton\pd296\1\PD296.prt", Origin:=437, StartRow:= _
        1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(49 _
        , 1), Array(65, 1), Array(80, 1), Array(93, 1)), TrailingMinusNumbers:=True
    
    'Set-up Top Layout
    Columns("A:F").Select
    Columns("A:F").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=3
    Rows("1:25").Select
    Range("A25").Activate
    Selection.Delete Shift:=xlUp
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
    Range("A4").Select
    Selection.ClearContents
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],m/dd/yy)"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""m/dd/yy"")"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[3]"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(R[-1]C,"": "",R[-1]C[1],"" "",R[-1]C[2])"
    Range("A7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A7").Cut Destination:=Range("A6")
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[4]&R[-1]C[5]"
    Range("A1:A7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1:E7").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("F6").Select
    Selection.ClearContents
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Based on Report Run:"
    Range("D1").Select
    Selection.Cut Destination:=Range("E1")
    Range("E1").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "at:"
    Range("E2").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E1:F2").Select
    Selection.Cut Destination:=Range("H1:I2")
    Columns("E:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "Increase"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "Decrease"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = "Revised"
    Range("G9").Select
    ActiveCell.FormulaR1C1 = "YTD"
    Range("D10").Select
    Selection.AutoFill Destination:=Range("D10:G10"), Type:=xlFillDefault
    Range("D10:G10").Select
    Range("H14").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("B:G").Select
    Columns("B:G").EntireColumn.AutoFit
    Range("H8").Select
    Columns("B:B").ColumnWidth = 12
    Columns("B:G").Select
    Selection.ColumnWidth = 12
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "Explanation"
    Range("G10").Select
    Selection.AutoFill Destination:=Range("G10:H10"), Type:=xlFillDefault
    Range("G10:H10").Select
    Columns("H:H").ColumnWidth = 17.57
    Range("H13").Select
    Columns("H:H").ColumnWidth = 26.71
    Range("F1:G2").Select
    Selection.Cut Destination:=Range("G1:H2")
    Range("H4").Select
    ActiveWindow.SmallScroll Down:=-12
    
    'Scaffolding
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight
    Range("c3000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "x"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Dim RwLast As Long
    RwLast = Range("A65536").End(xlUp).Row
    Range("A1:A2").Select
    Selection.AutoFill Destination:=Range("A1:A" & RwLast), Type:=xlFillDefault
    Range("A1").Select
    ActiveCell.EntireRow.Insert
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Line"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Keep"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Desc"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Prior YTD"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Current Yr"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Current YTD"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Increase"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Decrease"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "YTD"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Explanation"
    Columns("C:D").Select
    Selection.Insert Shift:=xlToRight
    Range("D12").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[1],3)"
    Range("C12").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("e2000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "x"
    Range("c12:d12").Select
    Selection.Copy
    Range("C13:D13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("h2000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    
    ActiveCell.Offset(0, 3).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    
    
    'Eliminate Superfluous Info
    Columns("D:D").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Range("b12").Select
    ActiveCell.Formula = "=IF(OR(C12=401,AND(E10=""Sub-total"",D12=""475""),and(d12=""233"",f11=""----------------"")),""Delete"",IF(OR(AND(G12=""----------------"",E13=""Sub-total""),E11=""Margin Percent (Directs)"",E11=""Sub-total"",e11=""Total Directs"",E12=""Direct Labor"",E12=""Cost Per Directory"",E12=""Cost Per Stop"",E12=""Sub-total"",E12=""Distribution"",E12=""DIRECT OPER. COST"",E12=""Allocated Costs"",E12=""PR Load"",E12=""Total Directs"",E12=""Revenue"",E12=""Gross Margin"",E12=""Margin Percent (Sales)"",E12=""Margin Percent (Directs)"",E12=""Directories"",E12=""Stops"",E12=""Ops Statistics"",AND(ISNUMBER(C12),C12>1)),"""",""Delete""))"
    ActiveCell.Copy
    Range("b13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("B:B").Copy
    Range("B:B").PasteSpecial xlPasteValues
    Range("A1:L1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="Delete"
    Rows("13:13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=2
    
    Range("e2000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveCell.Offset(0, -3).Select
    ActiveCell.FormulaR1C1 = "x"
    Range("b12").Select
    ActiveCell.Formula = "=IF(E11=""Cost Per Stop"",""Delete"",IF(B11=""Delete"",""Delete"",""""))"
    ActiveCell.Copy
    Range("b13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("B:B").Copy
    Range("B:B").PasteSpecial xlPasteValues
    Selection.AutoFilter Field:=2, Criteria1:="Delete"
    Rows("13:13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=2
    'Set Print Area
    Dim RwLast4 As Long
    RwLast4 = Range("h65536").End(xlUp).Row
    'LastRowInJ = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row
    'ActiveSheet.PageSetup.PrintArea = "$A$5:$I$" & LastRowInJ
    ActiveSheet.PageSetup.PrintArea = "$E$1:$L$" & RwLast4
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$9:$11"
        .PrintTitleColumns = ""
    End With
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$9:$11"
        .PrintTitleColumns = ""
    End With
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 72
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 72
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    ActiveWindow.View = xlNormalView
    ActiveWindow.SmallScroll Down:=-21
    
    Dim wstSheet As Worksheet
    Set wstSheet = ActiveSheet
    With wstSheet
        With .PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        End With
    End With
 
    Range("F2").Select
    
    'Determine Nature of Rows (Box or Total)
    Range("I13").Select
    ActiveCell.Formula = "=IF(AND($E13=""Gross Margin"", ISBLANK(F13)),"""",IF(OR($E13=""Sub-total"",$E13=""Total Directs"",$E13=""Cost Per Directory"",$E13=""Cost Per Stop"",$E13=""Gross Margin"",LEFT($E13,6)=""Margin""),""Total"",IF($F13=""----------------"",""----------------"",if($F13=""****************"",""****************"",IF($C13>1,""Box"","""")))))"
    ActiveCell.Copy
    Range("J13").Select
    ActiveSheet.Paste
    Range("I13:J13").Copy
    Range("I14:J14").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    
   
'R[-1]C[4]
End Sub

After running that, I've tried both codes you to replace those cells. First This:

Rich (BB code): 
 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)
       Loop While Not C Is Nothing And stAdd <> C.Address
   End If
End With
End Sub

Then this:
Code: 
 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[COLOR=red][B]:=xlFormulas[/B][/COLOR][COLOR=black])[/COLOR]
  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)
       Loop While Not C Is Nothing And stAdd <> C.Address
   End If
End With
End Sub

I looking in all 3 cells where there should be a change (even though I know this was just for F) and no luck.
 
Upvote 0
First, although it can be useful, sometimes I HATE the macro recorder. Without going through your code, I would venture to say about 75% of the lines are not needed. Anyway, I tested the original code (with xlValues) as a stand alone macro and it DOES work. So try that on a sample Workbook without the other code. and see if you can get it to run

lenze
 
Upvote 0
OK, sorry for the lag in response (my company has a cicular busy period I just went through). Back to this project, I tried doing what you said by actually copying the whole sheet and pasting it as values into a new sheet. I then ran your suggested macro and still no luck - it didn't insert any formulas :(
 
Upvote 0
I don't know why it's not working, But this code
Code: 
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)
Loop While Not C Is Nothing And stAdd <> C.Address
End If
End With
End Sub
when run on a sheet puts the formula in every cell in Column "F" when Column "I" = Total

lenze
 
Upvote 0
That's so weird! When I start a new sheet and type the same words in the same order that exist in column I on the original sheet, it does in fact recognize the word "total" and place a formula in F like you say, however, when I copy and paste the same info from the original sheet onto a new sheet (regular AND special-values), even though everything is theorhetorically the same), it doesn't work...it's like it's tainted or something! Any ideas?
 
Upvote 0
In that case, your data may be tainted. Check your formula, Maybe it is returning
Code: 
" Total" or "Total "
You can also check the length of a cell in Column "I" that contains Total
Code: 
=LEN(I6)
should return 5!!

lenze
 
Upvote 0
I was able to get this solved on another thread. Just to finish up this one, the problem was that the ReplaceFormulas macro needed a reference to the sheet I was wanting it to evaluate. So, the final code was:

Code: 
Sub ReplaceFormulas()
Dim LR As Long
Dim myRng As Range
Dim C As Range
Dim stAdd As String
With Worksheets("PD296")
LR = Cells(Rows.Count, "I").End(xlUp).Row
Set myRng = Range("$I$2:$I" & LR)
End With
With myRng
Set C = .Find("Total", LookIn:=xlFormulas)
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)
Loop While Not C Is Nothing And stAdd <> C.Address
End If
End With
End Sub

Of course I'm needing to make some changes to the formula it's inserting in order for it to work the way I need it to, but the find and replace portion works like a charm. Hope this helps someone!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Find and Replacing text in formula via VBA
Replies
5
Views
746
Joe4
Joe4
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
345
RoryA
RoryA
G
  • Solved
Conditional Formatting based on Increase/Decrease of percentage
Replies
8
Views
6K
Peter_SSs
Peter_SSs
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
446
kckliodna
K
S
  • Solved
Optimizing Macro
Replies
8
Views
1K
sax2play
S

Forum statistics

Threads
1,215,228
Messages
6,123,747
Members
449,118
Latest member
kingjet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top