Find second blank cell after P14 and format its contents as a percentage

[suremac]

Greetings,

I need to find the second blank cell after cell 'P14' and format its contents as a percentage. I am presently using this code:

Range("P31").NumberFormat = "0%"

How can I specify the range so that it finds and formats the second blank row after P14 as a percentage? The problem occurs because I have data continuously appending a table, which shifts all the data down, and causes the wrong data points to be formatted.

Thanks,

 

[Scott Huish]

If the cell is blank, how will it have content?

 

[suremac]

Well, the cell itself needs to be formatted in the aforementioned manner, so that any data put there will be formatted as a percentage.

Thanks,

 

[JoeMo]

This will find and format the second blank cell in column P after P14.

Sub FindSecondBlankAfterP14()
Dim lR As Long, c As Range
lR = Range("P" & Rows.Count).End(xlUp).Row
If WorksheetFunction.CountA(Range("P15:P" & lR)) <= Range("P15:P" & lR).Rows.Count - 2 Then
    'there are at least two empty cells from P15 to last filled cell in column P
    For Each c In Range("P15:P" & lR - 1)
        If IsEmpty(c) Then
            ct = ct + 1
            If ct = 2 Then
                c.NumberFormat = "0%"
                Exit For
        End If
    Next c
ElseIf WorksheetFunction.CountA(Range("P15:P" & lR)) = Range("P15:P" & lR).Rows.Count - 1 Then
    'only one empty cell between P15 and last filled cell in column P
    Cells(lR + 1, "P").NumberFormat = "0%"
Else
    Cells(lR + 2, "P").NumberFormat = "0%"
End If
End Sub

 

[suremac]

It worked perfectly! Thank you very much!

 

[JoeMo]

It worked perfectly! Thank you very much!

You are welcome - thanks for the reply.