If Statement Not working With ActiveCell.Font.Size = 20 Then

Question

I'm trying to data scrape some information for a long list of data that I have. The beginning of each segment that I need to scrape from (the active cell that I need to activate) has a font size of 20. However, this loop is running and working but when the cell that I need to exit the loop on, it goes to the else again (making an endless loop). Below is my code:

Dim repeat As Boolean
repeat = True    

Do While repeat = True
   If ActiveCell.Font.Size = 20 Then
      repeat = False
   Else
      ActiveCell.End(xlDown).Activate
   End If
Loop

I want the program to exit the loop once the activecell has a font with the size of 20.

I would not use a loop here. You could use `Range.Find` with `SearchFormat`. Or add some other condition to exit the loop - i.e. when the last row is reached. — BigBen, Oct 10 '19 at 19:04
Even with a Do Until Loop it isn't recognizing the Font Size @MilesFett — Jason Eisert, Oct 10 '19 at 19:07
Side note: you want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Oct 10 '19 at 19:08
@BigBen What code would you recommend me using to find my next group of data? The only unique identifier is the Font Size but excel isn't recognizing the fonts for some reason — Jason Eisert, Oct 10 '19 at 19:09
`ActiveCell.End(xlDown).Activate` probably jumps down past what you are looking for. See @BigBen 's first comment on using `Range.Find` — cybernetic.nomad, Oct 10 '19 at 19:11
Is the data always in a specific column (e.g. A), or are you relying on selecting a cell and getting the column from that? — BigBen, Oct 10 '19 at 19:14
@BigBen The reason I cant use the end(xldown) function to Activate the next needed cell is because there is unnecessary data in some of the clusters of data and not in others. — Jason Eisert, Oct 10 '19 at 19:18
It actually might be simpler to just use a normal `For` loop here. — BigBen, Oct 10 '19 at 19:34
For sure.... I guess I am just very confused as to why `ActiveCell.Font.Size = 20` isn't recognizing the Font Size? Even with a `For` loop if it doesn't recognize what I'm asking it to look for it won't work @BigBen — Jason Eisert, Oct 10 '19 at 19:41
Your `For` loop answer works but doesn't stop on the cells with the font size of 20 — Jason Eisert, Oct 10 '19 at 19:46
It isn't exiting. It became an endless loop because the code isn't recognizing the cell with a font size of 20 — Jason Eisert, Oct 10 '19 at 19:48
There's no way that for loop can be an endless loop. Did you modify in some way? — BigBen, Oct 10 '19 at 19:49
I didn't no... ahh I see it exits at the last row. It never found any results though — Jason Eisert, Oct 10 '19 at 19:50
No repro. Are you sure the font size is 20 (and not some fraction on the end)? Is the right sheet active? — BigBen, Oct 10 '19 at 19:51
You were right! For some reason, it displayed as 20 but is really 19.8.... weird — Jason Eisert, Oct 10 '19 at 20:06

score 1 · Accepted Answer · answered Oct 10 '19 at 19:42

If you are using Range.Find with SearchFormat, it might look something like this:

Sub Macro1()
    Application.FindFormat.Clear
    Application.FindFormat.Font.Size = 20

    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim foundCell As Range
    Set foundCell = ws.Range("A:A").Find(What:="", _
                                         After:=ws.Cells(ws.Rows.Count, 1), _
                                         LookIn:=xlFormulas, _
                                         LookAt:=xlPart, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=False, _
                                         SearchFormat:=True)

    If Not foundCell Is Nothing Then
        Debug.Print foundCell.Address '<- foundCell is the one you want
    End If

End Sub

If you want a regular For loop:

Dim ws as Worksheet
Set ws = ActiveSheet

Dim lastRow as Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Dim i as Long
For i = 1 to lastRow
    If ws.Cells(i, 1).Font.Size = 20 Then 
        Exit For '<- Cells(i, 1) is the one you want
    End IF
Next i

If Statement Not working With ActiveCell.Font.Size = 20 Then

1 Answers1