For Next loop skipping blank cell, but indexes past next cell

[CADim_JParker]

All,

I decided to make my first post a fun one.

I am working on some code to essentially find any cell in a given range that has text and append .01 to the end of it, but need to skip any cell that is blank or null.

Currently this is working ok, except when it skips a blank cell lets say row 9 and goes to the Next SelRange, it skips to row 11.

Code:
Sub RevPropUpdate()

Dim SelRange As Range
Dim ColNum As Integer
Dim CWS As Worksheet, TmpWS As Worksheet

'Find the column number where the column header is
Set CWS = ActiveSheet
ColNum = Application.WorksheetFunction.Match("Revision", CWS.Rows(1), 0)

'Set the column range to work with
Set SelRange = CWS.Columns(ColNum)


'Add a worksheet to put '1' onto the clipboard, ensures no issues on activesheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set TmpWS = ThisWorkbook.Worksheets.Add
With TmpWS
.Cells(1, 1) = 1
.Cells(1, 1).Copy
End With

'Select none blank cells using special cells...much faster than looping through all cells
Set SelRange = SelRange.SpecialCells(xlCellTypeConstants, 23)
SelRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
TmpWS.Delete
CWS.Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

'Adjust range to remove header row
Set SelRange = SelRange.Offset(1, 0)
SelRange.Select


'Modify each cell within the selected range
For Each SelRange In Selection
If IsEmpty(SelRange) Then
GoTo NextCell
ElseIf Not IsNull(SelRange.Value) Then
SelRange.Value = SelRange.Value & ".01"
End If
NextCell:
Next SelRange

End Sub

 

[Special-K99]

can't you change the loop to simply

For Each SelRange In Selection
If Not IsNull(SelRange.Value) Then
           SelRange.Value = SelRange.Value & ".01"
       End If
Next SelRange

 

[Micron]

I thought so too but when I tried that and the posted For Next block I did not experience the same issue either way. It worked fine. I wonder if it has to do with the setting and selecting of the range object but I can't see why.

@CADim_JParker please paste your code within code tags (click vba button on posting toolbar then paste within the resulting tags)

 

[NoSparks]

I believe when you offset your SelRange it moves your selections and non-selections down one row
so you now have the original non-selected blank selected and the originally selected cell below it now not selected.
Therefore the blank is being processed and the non-blank cell isn't.

 

[CADim_JParker]

can't you change the loop to simply

For Each SelRange In Selection
If Not IsNull(SelRange.Value) Then
           SelRange.Value = SelRange.Value & ".01"
       End If
Next SelRange

Thank you, but when I do that, it then adds .01 to the blank cells, which I want to remain blank.

 

[CADim_JParker]

I thought so too but when I tried that and the posted For Next block I did not experience the same issue either way. It worked fine. I wonder if it has to do with the setting and selecting of the range object but I can't see why.

@CADim_JParker please paste your code within code tags (click vba button on posting toolbar then paste within the resulting tags)

Will do when posting code, thanks.

 

[CADim_JParker]

I believe when you offset your SelRange it moves your selections and non-selections down one row
so you now have the original non-selected blank selected and the originally selected cell below it now not selected.
Therefore the blank is being processed and the non-blank cell isn't.

You are correct. I commented out the Offset and it works, but then it adds .01 to the first cell (header) that I am trying to remove from the selection to avoid having to rename it back. But that may be a simpler fix then correcting all the cells that are not processed.

I dug around trying to find a good solution to remove that top cell and this was the most common theme I could find.

 

[NoSparks]

Personally, I wouldn't be selecting anything, and I doubt dealing with the empty cells would be noticeable.
Here's what I would use if it were my project

Sub UpdateRevProp()

Dim rng As Range, cel As Range
Dim ColNum As Long
Dim CWS As Worksheet

Set CWS = ActiveSheet

Application.CutCopyMode = False

Application.ScreenUpdating = False

With CWS
    ColNum = Application.WorksheetFunction.Match("Revision", .Rows(1), 0)
    Set rng = Range(.Cells(2, ColNum), .Cells(.Rows.Count, ColNum).End(xlUp))
End With

For Each cel In rng
    If Not IsEmpty(cel) Then
        cel.Value = cel.Value & ".01"
    End If
Next cel

Application.ScreenUpdating = True

End Sub