Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adding automatic Formula if condition is true (excel)

    I am trying to add an addition to an macro steve helped me with,

    here it is:
    Option Explicit
    Sub HighlightLines()
    Dim sSearch As String
    Dim sAddress As String
    Dim rng As Range
    Dim rCell As Range
    sSearch = "Temp Gradient(C/M)"
    Set rng = Range(Range("d1"), Range("D65536").End(xlUp))
    With rng
    Set rCell = .Find(sSearch, LookIn:=xlValues)
    If Not rCell Is Nothing Then
    sAddress = rCell.Address
    Do
    With rCell.EntireRow.Range("A1:N1").Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    Set rCell = .FindNext(rCell)
    Loop While Not rCell Is Nothing _
    And rCell.Address <> sAddress
    End If
    End With
    Set rCell = Nothing
    Set rng = Nothing
    End Sub

    When the macro finds "Temp Gradient (C/M)" it will highlight in yellow (macro above) and calculate the following formula:

    Sub formula()

    Range("G47").Select
    ActiveCell.FormulaR1C1 = "=(R[-1]C/0.001+(9.81*R[-5]C))/R[-5]C"
    Range("G47").Select
    Selection.AutoFill Destination:=Range("G:O"), Type:=xlFillDefault
    Range("G:O").Select
    End Sub

    how do I add this into my above macro, should I call Formula() or place it in so there in just 1 macro

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding automatic Formula if condition is true (excel)

    Have you tried running one macro, then after its finished, run your formula one?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: adding automatic Formula if condition is true (excel)

    How about this? [But are you sure you want G:O and not F:N as in your example?]

    I am assuming you want it in every row below the highlighted rows (that contain the text in Col D)

    <pre>Option Explicit
    Sub HighlightLinesFormula()
    Dim sSearch As String
    Dim sAddress As String
    Dim rng As Range
    Dim rCell As Range
    sSearch = "Temp Gradient (C/M)"
    Set rng = Range(Range("d1"), Range("D65536").End(xlUp))

    With rng
    Set rCell = .Find(sSearch, LookIn:=xlValues)
    If Not rCell Is Nothing Then
    sAddress = rCell.Address
    Do
    rCell.EntireRow.Range("g11").Offset(1, 0). _
    FormulaR1C1 = _
    "=(R[-1]C/0.001+(9.81*R[-5]C))/R[-5]C"
    With rCell.EntireRow.Range("A1:N1").Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    Set rCell = .FindNext(rCell)
    Loop While Not rCell Is Nothing _
    And rCell.Address <> sAddress
    End If
    End With

    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


    Steve
    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> (This is a follow up question to the <post#=483583>post 483583</post#>. Please, in the future, post a reply in the original thread, do not start a new post for a followup question)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •