Why it works only on the first iteration? (1 Viewer)

[prabha_friend]

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

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

With Hope,
Prabhakaran

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

 

[The_Doc_Man]

Invariably you and others do this same thing all the time. In common language, what is the purpose/intent/goal of the code snippet? We need to know what you were intending to do.

If this is suppose to make mayonnaise then it isn't going to work. (No eggs.) But if it is supposed to do something else, maybe it could be made to work and we could advise you better on how to do that.

 

[prabha_friend]

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

 

[sonic8]

How to make it work on the following iterations too... Please Help.
[...]
I am getting a 'Type-Mismatch' Error from the 2nd iteration itself.

I don't know where the Type-Mismatch is coming from.
However, there is a general error in you code. IsEmpty(ArrayFromFilter) will never be true. Even if the Filter function found nothing, it will return an empty array. IsEmpty is checking for uninitialized Variant variables! If a variable contains an empty array, it is not uninitialized!

 

[prabha_friend]

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

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

 

[sonic8]

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

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

Most likely because UsedRange.Value then returns an array of values, which is not what the Array functions expects.

 

[cheekybuddha]

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

Is this code in a Word document module?

Is Filter() a Word VBA method?

 

[Josef P.]

Filter is a VBA function. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function

Example:

Dim filteredValues() As String
Dim SourceArray() As Variant

SourceArray = Array("abc", "xyz", "cba", "xxx")

filteredValues = Filter(SourceArray, "a", True, vbTextCompare)

Dim x As Variant
For Each x In filteredValues
   Debug.Print x
Next

This will not work: (see #5 + answer from Philipp #6)

Dim filteredValues() As String
Dim SourceArray() As Variant

SourceArray = Array("abc", "xyz", "cba", "xxx")

filteredValues = Filter(Array(SourceArray), "a", True, vbTextCompare) ' <--- Array + 1. item of array is an array

Dim x As Variant
For Each x In filteredValues
   Debug.Print x
Next

 

[cheekybuddha]

OK, I see Filter() is just an Office VBA function

 

[cheekybuddha]

Filter is a VBA function

Thank you - just found it!

 

[Josef P.]

I'm picky about language elements:
Filter is a function inside Strings module of VBA not from any Office library.

 

[cheekybuddha]

I'm picky about language elements: Filter is a function inside Strings module of VBA not from any Office library. View attachment 110437

Quite so.

 

[NauticalGent]

You guys are nerds ...which is a GOOD thing for floaters like me!