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
– Matt Cremeens, Commented Sep 12, 2016 at 15:28
Try adding at the end of that function CleanCode = strResult. — Matt Cremeens
– Matt Cremeens, Commented Sep 12, 2016 at 15:29

Shai Rado · Accepted Answer · 2016-09-12 17:30:24Z

1

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

answered Sep 12, 2016 at 17:30

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shai Rado Over a year ago

@indofraiser your'e welcome :) thanks for marking as answer

Collectives™ on Stack Overflow

Object Required Error With Excel VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related