Why it works only on the first iteration?

[Prabhakaran_Karuppaih]

Sub Operation_PWA_Members()
Dim xlApp As New Excel.Application
xlApp.Visible = True
Dim xlSheet As Worksheet
Set xlSheet = xlApp.Workbooks.Open(Replace(ThisDocument.Path & "\" & ThisDocument.Name, "docm", "xlsm")).Worksheets(1)
With xlSheet
    .Range("A1").Value = "Font_Name"
    For Each Character In ThisDocument.Characters
        With Character
            Dim ArrayFromFilter As Variant
            ArrayFromFilter = Filter(Array(xlSheet.UsedRange.Value), .Font.Name)
            If Not IsEmpty(ArrayFromFilter) Then
                xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Value = .Font.Name
            End If
        End With
    Next Character
End With
End Sub

p.s:
I am getting a 'Type-Mismatch' Error from the 2nd iteration itself.

How to make it work on the following iterations too... Please Help.

With Hope,
Prabhakaran

 

[duggie33]

Are you doing this in Excel? I don't think "ThisDocument" is valid, or at least I have never used it...

I see you are doing "Dim ArrayFromFilter..." inside a loop. That might cause an error.

Doug

 

[Prabhakaran_Karuppaih]

There is a word document presently typed in our mother tongue (Tamil) but by using different encoding methods at different places.
My Goal is to convert the whole document in Unicode.
That's why First I am making list of fonts used in the present document. To make a list in excel (unique list) we need to know whether the current character's font is already listed or not. That's why Filter Function.
Filter function (Visual Basic for Applications) | Microsoft Learn

 

[RoryA]

Array(xlSheet.UsedRange.Value) will work when there is only one value as it will create an array with one value in it. When there is more than one value, the `Usedrange.value` will return a 2D array directly, which you then wrap in another array, and Filter can't work with that. I'd suggest something like:

Sub Operation_PWA_Members()
Dim xlApp As New Excel.Application
xlApp.Visible = True
Dim xlSheet As Worksheet
Set xlSheet = xlApp.Workbooks.Open(Replace(ThisDocument.Path & "\" & ThisDocument.Name, "docm", "xlsm")).Worksheets(1)
With xlSheet
    .Range("A1").Value = "Font_Name"
    For Each Character In ThisDocument.Characters
        With Character
            Dim vMatch
            vmatch = xlapp.match(.Font.Name, xlsheet.range("A:A"),0)
            If IsError(vmatch) Then
                xlSheet.Cells(xlsheet.rows.count, "A").end(-4162).Offset(1, 0).Value = .Font.Name
            End If
        End With
    Next Character
End With
End Sub

 

[Prabhakaran_Karuppaih]

xlapp.match is depreciated in office 2021 I believe. Please confirm.

 

[RoryA]

No, it is not.

 

[Prabhakaran_Karuppaih]

Dim ArrayFromFilter As Variant
ArrayFromFilter = Filter(Array(xlSheet.UsedRange.Value), .Font.Name)

2nd line in 2nd iteration throws 'Type-Mismatch'

 

[RoryA]

You already said that, and I already explained why. Did you try the code I suggested?