Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find sometimes ignores Application.FindFormat
I use Excel 2003.
I have some data, which I intend to mark with a find, findnext loop. I find that neither bold, nor italic marks work. I have constructed some fairly simple code by editing a macro I recorded. I believe that find should not match a bold cell, but it does. ;( This is my code, which is stand alone. Option Explicit Sub Macro1() ' Workbooks.Add Cells(1, 1).Value = "foo" Cells(2, 1).Value = "fubar" Cells(3, 1).Value = "fubar" Cells(4, 1).Value = "foo" Cells(2, 1).Font.Bold = True Application.FindFormat.Clear Application.FindFormat.NumberFormat = "General" With Application.FindFormat .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Application.FindFormat.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Application.FindFormat.Borders(xlLeft).LineStyle = xlNone Application.FindFormat.Borders(xlRight).LineStyle = xlNone Application.FindFormat.Borders(xlTop).LineStyle = xlNone Application.FindFormat.Borders(xlBottom).LineStyle = xlNone Application.FindFormat.Borders(xlDiagonalDown).Lin eStyle = xlNone Application.FindFormat.Borders(xlDiagonalUp).LineS tyle = xlNone Application.FindFormat.Interior.ColorIndex = xlNone Application.FindFormat.Locked = True Application.FindFormat.FormulaHidden = False Cells(1, 1).Activate Cells.Find(What:="fubar", LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=True).Activate Debug.Print ActiveCell.Address & ", FoundStyle = " & _ ActiveCell.Font.FontStyle _ & ", DesiredStyle = " & Application.FindFormat.Font.FontStyle End Sub It writes $A$2, FoundStyle = Bold, DesiredStyle = Regular I don't understand why it does not write $A$3, FoundStyle = Regular, DesiredStyle = Regular I have not found any URL which is a good example of FindFormat. I hope the mavens here can enlighten me. Please? -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find sometimes ignores Application.FindFormat
I suspect the FindFormat feature only grabs stuff that's not 'regular'
formatting, or otherwise what's defined in 'Normal' Style! This is just my assumption based on the behavior of your code example... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find sometimes ignores Application.FindFormat
Hi Walter,
Am Fri, 4 Jul 2014 22:20:27 +0100 schrieb Walter Briscoe: I use Excel 2003. I have some data, which I intend to mark with a find, findnext loop. I find that neither bold, nor italic marks work. I have constructed some fairly simple code by editing a macro I recorded. I believe that find should not match a bold cell, but it does. ;( I had the same problem with xl2007 or later and I could manage it if I create a cell e.g. Z1 with the expected formats and refer to that cell, e.g. With Application.FindFormat.Font .Name = Range("Z1").Font.Name .FontStyle = Range("Z1").Font.Fontstyle .Size = Range("Z1").Font.Size |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find sometimes ignores Application.FindFormat
In message of Sat, 5 Jul 2014 10:51:26
in microsoft.public.excel.programming, Claus Busch <claus_busch@t- online.de writes Hi Walter, Am Fri, 4 Jul 2014 22:20:27 +0100 schrieb Walter Briscoe: I use Excel 2003. I have some data, which I intend to mark with a find, findnext loop. I find that neither bold, nor italic marks work. I have constructed some fairly simple code by editing a macro I recorded. I believe that find should not match a bold cell, but it does. ;( I had the same problem with xl2007 or later and I could manage it if I create a cell e.g. Z1 with the expected formats and refer to that cell, e.g. With Application.FindFormat.Font .Name = Range("Z1").Font.Name .FontStyle = Range("Z1").Font.Fontstyle .Size = Range("Z1").Font.Size . . . End With Thanks Claus and also to Gary for shedding some light. A quick check suggests the problem is cured in Excel 2010. (I borrowed my son's machine. ;) I find the following Property usage applies: (I got the list of properties with Dim P As CellFormat ' So locals window can see 2nd cell members Set P = Application.FindFormat ) Symbol Meaning ? Not checked N Ignored in Application.FindFormat Y Works in Application.FindFormat With Application.FindFormat .AddIndent ? .Application ? .Borders ? .Creator ? With .Font .Application ? .Background ? .Bold N .Color ? .ColorIndex Y .Creator ? .FontStyle N .Italic N .Name Y .OutlineFont ? .Parent ? .Shadow ? .Size Y .Strikethrough ? .Subscript ? .Superscript ? .Underline N End With .FormulaHidden ? .HorizontalAlignment ? .IndentLevel ? .Interior ? .Locked ? .MergeCells ? .NumberFormat Y .NumberFormatLocal ? .Orientation ? .Parent ? .ShrinkToFit ? .VerticalAlignment ? .WrapText ? End With -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PLEASE HELP! Application.speech - Everyone always ignores thisquestion | Excel Programming | |||
losing Application.FindFormat.NumberFormat after finding first cel | Excel Programming | |||
FindFormat with Borders not Working | Excel Discussion (Misc queries) | |||
Findformat | Excel Programming | |||
FindFormat | Excel Programming |