Jason Eisert
Jason Eisert

Reputation: 3

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

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.

Upvotes: 0

Views: 81

Answers (1)

BigBen
BigBen

Reputation: 49998

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

Upvotes: 1

Related Questions