Object Required Error With Excel VBA

Question

I've used How to remove all non alphanumeric characters from a string except period and space in excel? but keep getting

Object required

You can see around the text where I have tried various Err or If statements but these have sadly not fixed it.

Sub CleanAll()

Set wb = Workbooks("test.xlsm")
Dim rng As Range
' Dim i As Integer
' i = 1

For Each rng In wb.Sheets("Portal_Aligned").Range("A1:AX9999").Cells 'adjust sheetname and range accordingly
    rng.Value = CleanCode(rng.Value) & ""
    '   i = i + 1
Next

End Sub

Function CleanCode(strSource As String) As String

'On Error GoTo Err1
'http://www.asciitable.com/

Dim i As Integer
Dim strResult As String

' If strSource <> "" Then
For i = 1 To Len(strSource)
    Select Case Asc(Mid(strSource, i, 1))

        Case 32 To 33, 35 To 131, 133 To 135, 145 To 146, 150 To 152, 155, 162 To 166, 183, 188 To 190, 247
            strResult = strResult & Mid(strSource, i, 1)
    End Select
Next

'AlphaNumericOnly = strResult
'rng.Value = AlphaNumericOnly(rng.Value)
'  End If
Err1:

End Function

It doesn't appear as though `CleanCode` is returning anything. — Matt Cremeens, Sep 12 '16 at 15:28
Try adding at the end of that function `CleanCode = strResult`. — Matt Cremeens, Sep 12 '16 at 15:29

score 1 · Accepted Answer · answered Sep 12 '16 at 17:30

The code below (tested) will work, keep in mind, with all the Case you allowed, there are few characters which you are limiting (like double quotes ").

In the reference link you've provided, the PO restricts a few more (like : , ;, = , >, and more).

Sub CleanAll()

Set wb = Workbooks("test.xlsm")
Dim rng             As Range

For Each rng In wb.Sheets("Portal_Aligned").Range("A1:AX9999") 'adjust Sheet Name and range accordingly
    ' handle cells with emprty strings here
    If rng.Value <> "" Then
        rng.Value = CleanCode(rng.Value) & ""
    End If
Next

End Sub

Function CleanCode(strSource As String) As String

Dim i               As Integer
Dim strResult       As String

For i = 1 To Len(strSource)

    Select Case Asc(Mid(strSource, i, 1))

        Case 32 To 33, 35 To 131, 133 To 135, 145 To 146, 150 To 152, 155, 162 To 166, 183, 188 To 190, 247
            strResult = strResult & Mid(strSource, i, 1)

    End Select
Next

CleanCode = strResult

End Function

@indofraiser your'e welcome :) thanks for marking as answer – Shai Rado Sep 13 '16 at 07:00 — Shai Rado, Sep 13 '16 at 07:00

Object Required Error With Excel VBA

1 Answers1