Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: .Formula1 error in logging Conditional Formatting

  1. #1
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default .Formula1 error in logging Conditional Formatting

    I'm logging by conditional formatting based on Jeff Weir's work (Sub FindCF() here). I kludged together a way to log it to a sheet using the code below:

    Code:
    For Each WhatIsIt In sht.Cells.FormatConditions
            Select Case TypeName(WhatIsIt)
                Case "FormatCondition"
                Set fc = WhatIsIt
                sht.Cells(Rows.Count, 1).End(xlUp).Offset(1) = _
                "Worksheet:§" & sht.name & _
                "§Type:§" & TypeName(WhatIsIt) & " | " & cfType(WhatIsIt.Type) & _
                "§Applies To:§" & Replace(WhatIsIt.AppliesTo.address, "$", vbNullString) & _
                "§Formula:§" & WhatIsIt.Formula1 & _
                "§Stop if True:§" & WhatIsIt.StopIfTrue
    and then running Text to Columns using "§" as a delimiter.

    However, when I attempted to tighten the code up, the WhatIs.Formula1 starts giving RTE 1004 (Application-defined or object-defined error) when I use the code below:
    Code:
              Case "FormatCondition"
                With shtLog.Cells(Rows.Count, 1).End(xlUp)
                  Set fc = WhatIsIt
                  .Offset(1, 0) = sht.name ' sht name
                  .Offset(1, 1) = TypeName(WhatIsIt)  ' type
                  .Offset(1, 2) = cfType(WhatIsIt.Type)  ' Type of CF
                  .Offset(1, 3) = Replace(WhatIsIt.AppliesTo.address, "$", vbNullString)  ' address
                  .Offset(1, 4) = WhatIsIt.Formula1  ' Formula of CF
                  .Offset(1, 5) = WhatIsIt.StopIfTrue  ' bln - Stop if True
                End With
    I've attempted to use cf.Formula1 (like Jeff uses), but that doesn't help; I've attempted to point it back to the sheet with the actual CF and that doesn't help. Interestingly, in the Immediate Window, ?WhatIsIt.Formula1 gives the proper answer. I'm stumped!

    Any suggestions to fix the error (or a better way to log my CF to a sheet) would be most welcome!!

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: .Formula1 error in logging Conditional Formatting

    The formula is probably erroring when you put it in the cell so try prefixing it with an apostrophe:
    Code:
    .Offset(1, 4).Value2 = "'" & WhatIsIt.Formula1

  3. #3
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: .Formula1 error in logging Conditional Formatting

    I bow before your genius, sir!

    Thanks so much, Rory!! That works perfectly (and makes sense too).

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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