none
sheet-conditional find/replace RRS feed

  • Question

  • This is a little tricky.

    Need a Sub that will ...

     - Select all sheets in a workbook where sheet name contains an underscore.

     - Select all of column b in the selected sheets.

     - Find and replace = for '=.

     - Then find and replace + for '+.

    Obviously a way around imported txt data.

    Thank you .

     - Mik

    Friday, November 27, 2015 5:07 AM

Answers

  • Try this:

    Sub Tricky()
        Dim wsh As Worksheet
        For Each wsh In Worksheets
            If InStr(wsh.Name, "_") > 0 Then
                With wsh.Range("B:B")
                    .Replace What:="=", Replacement:="'=", LookAt:=xlPart
                    .Replace What:="+", Replacement:="'+", LookAt:=xlPart
                End With
            End If
        Next wsh
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 27, 2015 8:56 AM

All replies

  • Try this:

    Sub Tricky()
        Dim wsh As Worksheet
        For Each wsh In Worksheets
            If InStr(wsh.Name, "_") > 0 Then
                With wsh.Range("B:B")
                    .Replace What:="=", Replacement:="'=", LookAt:=xlPart
                    .Replace What:="+", Replacement:="'+", LookAt:=xlPart
                End With
            End If
        Next wsh
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 27, 2015 8:56 AM
  • Hans, thank you.

    Good as ever ..!

    Monday, November 30, 2015 1:59 AM