Consulting

Results 1 to 11 of 11

Thread: ".findformat.font.Italic=false" code needed

  1. #1

    ".findformat.font.Italic=false" code needed

    Hallo, hope you are all well.

    Does anybody have a way of using the .find method of VBA (with XL 2007) to locate the next cell down (in a column) containing text without special formatting (in my case specifically: without italics)? I'm trying to avoid looping through and testing each cell in turn.

    As always, thanks for your attention.

    Ken

  2. #2
    How do you have that in mind if the cell should not be individually tested?

    If you can't find a better way, you might have to resort to something like this.
    Sub Maybe()
    Dim c As Range, rng As Range
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If c.Font.Italic = True Then    '<---- Change True to False for the other way around
                If rng Is Nothing Then
                    Set rng = c
                        Else
                    Set rng = Union(rng, c)
                End If
            End If
        Next c
    rng.Select    '<--- Do here what you need to do with the italics
    End Sub
    Last edited by jolivanes; 03-10-2020 at 12:38 PM. Reason: Add code

  3. #3
    Hi, jolivanes, thanks for helping.

    The reason I'm trying to avoid the kind of loop you are suggesting is that my macro has to cope with so many of them that it is starting to limp rather badly. Using the .Findformat.Font.Bold=True method, I have already achieved some success; but now I need to jump over rows that are formatted in italics (and at Indentlevel 1) and I can't find keywords that VBA can "understand". It seems strange to me that ...Font.Italic=False doesn't work and neither does ...Font.Standard=True. Do you think ...Indentlevel=1 will get me where I'm going?
    Gratefully,
    Ken

  4. #4
    We have not seen the rest of your code. Maybe that can be streamlined?
    An explanation of what you want to achieve, from beginning to end, might help.
    An attachment with a before and after including explanations on how you derived at your "after" is usually the best.

  5. #5
    Well, jolivanes, I would not like to ask anybody to think their way into my code as the complexities are bewildering if one is not very familiar with the background of the data. My problem boils down to the following, however:
    In a column of items with standard font format (neither bold nor italic) it is easy enough to search down the column to find the first cell with text formatted in italics
    HTML Code:
    Application.Findformat.Clear
    Application.FindFormat.Font.Italic=True
    RowOfFirstItalicItem=Range(rngColumn.Find(what:="*",after:=Activecell,searchformat:=true).row
    BUT how do I do that the other way round, i.e. in a column of items in italics find the first cell not formatted in italics. Once I've located the cell in question my code with pull all sorts of data from the table for processing. Your method of rounding up cell-ranges into a union works fine and in other parts of my code I've used it to good effect, but I'm trying to replace For-Next loops and Do While/Until loops with faster processes.

    Any thoughts on that, anyone?

    Thanks again,

    Ken

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I can't make it find not-italics!
    You may be stuck with looking for the indent:
    Application.FindFormat.IndentLevel = 1
    Dim c As Range
    With Columns("A:A")
      Application.FindFormat.Clear
      Application.FindFormat.IndentLevel = 1
      Set c = .Find(What:="", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
      If Not c Is Nothing Then
        firstcell = c.Address
        Do
        c.Select
        'do something with c here
          Set c = .Find(What:="", after:=c, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
        Loop Until c.Address = firstcell
      End If
    End With
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Hi, p45cal.
    Thanks for trying; if a guru like yourself can't make vba jump through that hoop, I can stop cudgelling my brains on that score. I did try the idea using .indentlevel=1 to locate the cell I was looking for, but it didn't seem to work. I'll give it another shot later today checking my code against yours since my codes have been known to contain errors!

    Have a good day,

    Ken

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    If you were to attach a small file with the cells that need examining we could have a look and see how the indents have been applied.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Quote Originally Posted by p45cal View Post
    If you were to attach a small file with the cells that need examining we could have a look and see how the indents have been applied.
    Hallo again, p45cal,

    the Excel file in question is on a windows system on an internet segregated PC. The best I can do at the moment is to create a sample on the Mac system I use for e-communication and learn how to attach it to my reply here. 'Scuse me a moment while I do some background reading.....Oh; I think I can manage that, let's see.
    Attached Files Attached Files

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    If I run this when your attached file/sheet is active:
    Sub blah1()
    Dim c As Range
    With Range("A1").CurrentRegion.Columns(1)
      Application.FindFormat.Clear
      Application.FindFormat.IndentLevel = 0
      Set c = .Find(What:="", after:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
      If Not c Is Nothing Then
        firstcell = c.Address
        Do
        c.Select
        'do something with c here
        MsgBox c.Address
          Set c = .Find(What:="", after:=c, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
        Loop Until c.Address = firstcell
      End If
    End With
    End Sub
    it finds all EXCEPT row 13. (A13)
    Turns out this is because it's the only cell with no indent that also has left align applied. Remove that left align and it's found. Agreed, this is no good.

    I can't think of a non-looping-through-each-cell approach to this, however, if you can arrange only to loop through the range once to determine indents (all of them or just the 0 indents) and keep that information in memory (or even in cells in a fresh column) and use when needed you could reduce the impact of looping.
    It all depends on what you're trying to do. I would tend to go for a fresh column holding the indent level of each row, which can be done with a udf or with a macro to populate that column. Then you can use that columns data to filter, make a pivot, whatever.
    The following will leave you with an array in zz containing all the row numbers with 0 indents, regardless of the presence of left-align, at the same time it will leave you with a range (c) which is only the cells with zero indent in column A:
    Sub blah2()
    Dim c As Range
    With Range("A1").CurrentRegion.Columns(1)
      ReDim zz(1 To .Rows.Count)
      i = 0
      For Each cll In .Cells
        If cll.IndentLevel = 0 Then
        If c Is Nothing Then Set c = cll Else Set c = Union(c, cll)
          i = i + 1
          zz(i) = cll.Row
        End If
      Next cll
      ReDim Preserve zz(1 To i)
    End With
    If not c is nothing then c.select
    End Sub
    and below will fill column F with the indent levels of column A:
    Sub blah3()
    With Range("A1").CurrentRegion.Columns(1)
      zz = .Value
      i = 0
      For Each cll In .Cells
        i = i + 1
        zz(i, 1) = cll.IndentLevel
      Next cll
      Range("F1").Resize(.Rows.Count) = zz
    End With
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    Great work, p45cal. Your post did the trick! I can locate the cell I need by checking on the indent level. Some other parts of the code you wrote I have also noted for future use.
    For those interested: my fault (and it is humbling to note that just about every time I post a problem here, it is caused by my overlooking something simple!) was as follows:
    like an idiot I had failed to notice that—unlike in the column I submitted to you—in the "actual" column all the cells were formatted with .indentlevel =1; no wonder then that it did not find the next cell down having .indentlevel=0. It was your remark, p45cal, that the horizontal alignment = xlLeft had caused a problem that caused me to check with more alertness just how things were formatted. So your efforts on my behalf were not entirely wasted, especially since other parts of the code you suggested were very enlightening. So thanks again for your time and attention, it was very helpful even though I actually pointed you in the wrong direction with the indent level-problem.

    Yours red-faced (as usual),

    Ken

Tags for this Thread

Posting Permissions

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