CODEWITHSUNDEEP
vbaexcel
Tan Stanley
Tan Stanley

Reputation: 101

Excel VBA Replacing Question Mark

I have VBA code written up to find and replace question marks in all worksheets in a workbook. However it is not working, can anyone help me out to see where did i go wrong? 

Sub ReplaceQM()

    Dim lRow As Long
    Dim lCol As Long

    totalSheet = ThisWorkbook.Sheets.Count

    MsgBox totalSheet
    For x = 1 To totalSheet


        lRow = ThisWorkbook.Sheets(x).Cells(Rows.Count, 1).End(xlUp).Row
        lCol = ThisWorkbook.Sheets(x).Cells(1, Columns.Count).End(xlToLeft).Column


        For Z = 1 To lRow
            For i = 1 To lCol

                getPos = InStr(1, ThisWorkbook.Sheets(x).Cells(Z, i).Value, "~?")

                If getPos > 0 Then

                    ThisWorkbook.Sheets(x).Cells(Z, i).Value = Replace(ThisWorkbook.Sheets(x).Cells(Z, i).Value, "~?", " ")

                End If

            Next i
        Next Z
    Next x
End Sub

Upvotes: 3

Views: 2881

Answers (1)

Roie R
Roie R

Reputation: 139

You're better off using Excel's in-range replace function:

For Each ws In ThisWorkbook
  ws.UsedRange.Cells.Replace what:="~?", Replacement:=" ", LookAt:=False, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next ws

Upvotes: 2

Related Questions