Find and Replace Within Range VBA

[jmazorra]

Hello everyone:

I wrote a VBA code that would allow me to find and replace $ for Y I in column I. But also I need to replace blank cells in column I with N. The problem I have is that it fills all the blank cells down with N and I only need the cells within a range filled. In other words, there are 24 rows of data from I2 down, then search for Blanks and Replace with N in those 24 rows.

At the end of the code below you can see that I got to half my VBA, but I cannot figure out the replace blanks with N within range.

Any help will be appreciated

Private Sub CommandButton1_Click()

Sheets("byposition").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("I:I").Select
Selection.Replace What:="$", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox "Step 1 Completed"
End Sub

 

[dave3009]

Hi

Maybe something like

Private Sub CommandButton1_Click()
Dim rws As Long
With Sheets("byemployee")
    .Rows("1:7").Delete Shift:=xlUp
    .Cells.Sort Key1:=Range("A2"), Order1:=xlAscending
End With
With Sheets("byposition")
    rws = .UsedRange.Rows.Count
    .Rows("1:7").Delete Shift:=xlUp
    .Columns("E:E").Copy
    .Columns("A:A").PasteSpecial
    .Cells.Sort Key1:=Range("A2"), Order1:=xlAscending
    .Columns("I:I").Replace What:="$", Replacement:="Y"
    .Range("I2:I" & rws).SpecialCells(xlCellTypeBlanks).Value = "N"
End With
MsgBox "Step 1 Completed"
End Sub

 

[jmazorra]

perfect, thanks