For If Statement

Question

I'm having an issue with my For If statement. The scenario is that, I need the empty cells to be filled with a telephone number. I have made a code and it works but at some point it will stop in between. Sometimes all the way to the end of the last row or just skip a few cells and continue again. I'm really not sure about this weird issue, hopefully someone can give me an insight. I have also attached a snapshot of the results.

My code is as shown below:-

    Dim pn As Range
    Dim h As Integer, phone As Integer
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    Set pn = Range("AE2:AE" & lastrow)
    h = 1

    For phone = 1 To pn.Rows.Count
       If pn.Cells(h) = "" Then
         pn.Cells(h) = "03-33422828"
       Else
         h = h + 1
       End If
    Next

Gosh! All this time my understanding on the For If statement implementation has been a bit wrong.. Thanks a lot BigBen! It worked accordingly :D — Khirxn
– Khirxn, Commented Aug 3, 2021 at 17:05
Even better, Dim cell As Range, For Each cell in pn, If cell.Value = "" Then, cell.Value = "03-33422828", End If. — BigBen
– BigBen, Commented Aug 3, 2021 at 17:06

Robson · Accepted Answer · 2021-08-03 16:33:54Z

2

An alternative way to do this is to select all the used cells in that range, then fill all the blank ones with a value.

For example, if we have a workbook with data in column A and we want to fill the blank cells in that column with "---" we could do this:

On Error Resume Next
Range("A1:A" & ActiveSheet.UsedRange.Rows.Count). _
  SpecialCells(xlCellTypeBlanks).Value = "---"
On Error GoTo 0

VBA will generate an error if there are no blank cells, so I've "handled" that by just ignoring any errors.

For you it would be more like:

On Error Resume Next
Range("AE1:AE" & ActiveSheet.UsedRange.Rows.Count). _
  SpecialCells(xlCellTypeBlanks).Value = "03-33422828"
On Error GoTo 0

edited Aug 3, 2021 at 16:33

answered Aug 3, 2021 at 16:22

Robson

2,0322 gold badges10 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Khirxn Over a year ago

Hey there Robson. This is actually a good alternative way, shorter way too! I've tried it and will also definitely implement this method in future. Thank you very much mate! :D

Collectives™ on Stack Overflow

For If Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related