CODEWITHSUNDEEP
excelvbasearchreplacewildcard
user3574547
user3574547

Reputation: 99

Search/Replace in One Column Impacting Entire Worksheet

In Column L (only) I want to replace any instance of data with "True" regardless of what was originally in any of the Column L cells. The code I tried was:

With ActiveSheet
  intLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Let strSelectRange = "L2" & ":" & "L" & intLastRow
  Range(strSelectRange).Select

Cells.Replace What:="*", Replacement:="True", LookAt:=xlPart _
  , SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

End With

First, I used .Rows.Count, "A" because in that column every row has data so I know how many rows to go down in Column L. In Column L many cells will be blank.

When I run this, every cell in the entire worksheet that has anything in it, is changed to True, not just the data in Column L.

Another method I tried was:

Range("L2:L1200").Select
Selection.Replace What:="*", Replacement:="True", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("A1").Select

What I don't like about this is that I picked L1200 as the number of rows just to be sure I'd search farther than the actual last row that can contain data. I'm worried that this method might cause some kind of problem at some point.

What I'd really like to know is what I'm doing wrong in the first code example.

Thanks for any help you can offer!!!

Upvotes: 1

Views: 79

Answers (2)

VBasic2008
VBasic2008

Reputation: 54948

Search & Replace in Column

  • Use Option Explicit always, to quicker learn about the occurring errors and to be forced to declare variables.
  • You should always declare your rows as Long.
  • When you use the With statement you use the dots on everything, even on .Range and .Cells etc. The code might work in this case (ActiveSheet) anyway, but it is incorrect.
  • Avoid the use of ActiveSheet, use the worksheet name.
  • Avoid the use of Select. There are many posts (articles) about this.
  • When ever you use Cells without anything behind it, it refers to all the cells in the worksheet.
  • The first thing in the Replace function (Find function) is the range where you're going to Replace (Find, Search). It can be a column, it can be Cells or just a smaller range.

The Code

Sub SROneColumn()

    Const cVntLRColumn As Variant = "A"  ' Last Row Column Letter/Number
    Const cVntCriteria As Variant = "L"  ' Criteria Column Letter/Number
    Const cLngFirstRow As Long = 2       ' First Row Number
    Const cStrReplace As String = "True" ' Replace String

    Dim lngLastRow As Long        ' Last Row Number
    Dim strSelectRange As String  ' Select Range Address

    With ActiveSheet
        lngLastRow = .Cells(.Rows.Count, cVntLRColumn).End(xlUp).Row
        strSelectRange = .Range(.Cells(cLngFirstRow, cVntCriteria), _
                .Cells(lngLastRow, cVntCriteria)).Address
        .Range(strSelectRange).Replace What:="*", Replacement:=cStrReplace, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
    End With

End Sub

An interesting way to use a worksheet without the use of an object variable:

Sub SRSheet()

    Const cStrSheet As Variant = "Sheet1"   ' Worksheet Name/Index

    With ThisWorkbook.Worksheets(cStrSheet)


    End With

End Sub

Upvotes: 1

pnuts
pnuts

Reputation: 59495

Range(strSelectRange).Select

selects a range (though best to avoid Select) but then your code does nothing with that selection because Cells is the entire sheet.

Maybe you want instead:

  Range(strSelectRange).Replace What:="*", Replacement:="True", LookAt:=xlPart

Upvotes: 1

Related Questions