VBA - Space Become Question Mark

[RudRud]

Hi, When i paste the code below, spaces will turn into question mark in my VBA

Please help

Option Compare Text
Sub tes2t()
Dim ws As Worksheet
Set ws = Sheets("sheet1")
Dim f As Range
Dim eptitle As Range
Dim i%
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Comparemode = vbTextCompare


For i = 2 To Worksheets.Count
    dict.Add Sheets(i).Name, i
Next i

i = 0
    lrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Set col = FindAll(ws.Range(Cells(1, "a"), ws.Cells(lrow, "k")), "CIN")
    Set col2 = FindAll2(ws.Range(Cells(1, "a"), ws.Cells(lrow, "k")), "EPISODE NO")
   
    For Each c In col2
        i = i + 1
        shtname = IIf(IsEmpty(col2(i).Offset(0, 1).Value), "EP0", "EP" & Replace(Replace(Trim(col2(i).Offset(0, 1).Value), "?", ""), "?", ""))
        If dict.Exists(shtname) Then
            ws.Range("A" & col(i).Row - 2 & ":K" & col(i).Row + 21).Copy Sheets(shtname).[a1]
        Else
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = shtname
            ws.Range("A" & col(i).Row - 2 & ":K" & col(i).Row + 21).Copy Sheets(shtname).[a1]
        End If
    Next c
   
End Sub
Public Function FindAll(rng As Range, val As String) As Collection
    Dim col As New Collection, f As Range
    Dim addr As String
    '## set `Find` arguments as required ###
    Set f = rng.Find(what:=val, After:=rng.Cells(rng.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If Not f Is Nothing Then addr = f.Address()
    Do Until f Is Nothing
        col.Add f
        Set f = rng.FindNext(After:=f)
        If f.Address() = addr Then Exit Do 'have looped back to start...
    Loop
    Set FindAll = col
End Function

Public Function FindAll2(rng As Range, val As String) As Collection
    Dim col2 As New Collection, f As Range
    Dim addr As String
    '## set `Find` arguments as required ###
    Set f = rng.Find(what:=val, After:=rng.Cells(rng.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If Not f Is Nothing Then addr = f.Address()
    Do Until f Is Nothing
        col2.Add f
        Set f = rng.FindNext(After:=f)
        If f.Address() = addr Then Exit Do 'have looped back to start...
    Loop
    Set FindAll2 = col2
End Function

 

[GlennUK]

I can paste that fine into my VBA. How did you do your Copy and Paste exactly?

 

[RudRud]

I can paste that fine into my VBA. How did you do your Copy and Paste exactly?

Its working fine now, Thanks for your kind reply.

I tried to format the computer and set into English Language (previous Chinese language)