Printable View
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
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.
Code: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
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
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.
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
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.HTML Code:Application.Findformat.Clear
Application.FindFormat.Font.Italic=True
RowOfFirstItalicItem=Range(rngColumn.Find(what:="*",after:=Activecell,searchformat:=true).row
Any thoughts on that, anyone?
Thanks again,
Ken
I can't make it find not-italics!
You may be stuck with looking for the indent:
Application.FindFormat.IndentLevel = 1
Code: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
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! :think:
Have a good day,
Ken
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,Quote:
Originally Posted by 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.
If I run this when your attached file/sheet is active:it finds all EXCEPT row 13. (A13)Code: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
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:and below will fill column F with the indent levels of column A:Code: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
Code: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
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