CODEWITHSUNDEEP
excelvba
Peerawat Wongsrikaew
Peerawat Wongsrikaew

Reputation: 1

Find and Replace an entire column for every sheet

I have tried to find and replace using a VBA for a specific column in every sheet.

What made me stuck is that I have a letter that should refer to a column but I can't find a way to do it.

For Example, I have a letter "I" in cell B5 and I want to find and replace an entire "I" column in every sheet. What Should I do?

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

Sheets("Sheet1").Select
fnd = Range("B1")
rplc = Range("B2")

For Each sht In ActiveWorkbook.Worksheets
  sht.Columns("I:I").Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

This is a code I got so far Thanks

Upvotes: 0

Views: 145

Answers (1)

CLR
CLR

Reputation: 12279

You already know how to take the value from B5 as you're doing it with B1 and B2. You just need to alter your code to use the value captured in B5 within your .Replace command.

Try this:

Dim sht As Worksheet
Dim fnd As String, rplc As String, colm As String

With Sheets("Sheet1")
    fnd = .Range("B1").Value
    rplc = .Range("B2").Value
    colm = .Range("B5").Value
End With

For Each sht In ActiveWorkbook.Worksheets
  sht.Columns(colm).Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next

You may want to consider wrapping your .Replace line in an If statement to ensure it doesn't run on Sheet1 (where your search criteria are).

If sht.name <> "Sheet1" Then
    sht.Columns(colm).Repl...etc
End If

Upvotes: 2

Related Questions