Question Why it works only on the first iteration?

[PrabhakaranKaruppaih]

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.

 

[Ralston18]

As always, you ( @PrabhakaranKaruppaih ) must do some work before posting questions and requesting help.

= = = =

Why do you believe the code only works on the first iteration?

What are the various values etc. when the code stops?

What does "Type-mismatch" mean? How would you explain that to someone?

Do a manual step through line by line pretending that you are the executing system. Write down each input, process, and output exactly as it would be if executed by your code.

Add in some comment lines to explain each step taking place and what each variable is and what any given process/action/function is supposed to be doing.

Start with a "Purpose" comment at the beginning to state what the overall code is expected to do and maybe even why.

Put in some test lines to present key variable and values as the code executes so you can observe the process and values. And verify that they are indeed (or not) as expected.

Provide an example of the input data and what the output is expected to be.

Show your work first.

 

[ex_bubblehead]

As above. You don't just post a block of code with no explanation as to what it is what it's supposed to do what you have already done to troubleshoot and then expect us to do your job of troubleshooting and fixing it. You have a penchant for asking others to do your job for you.

"Type-Mismatch" means exactly that, your program has encountered a value that does not match what it was expecting to find in that position.

 

[PrabhakaranKaruppaih]

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

 

[PrabhakaranKaruppaih]

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

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

 

[Ralston18]

Did you do a line by line walkthrough of your code?

You really need to do that.

Use a sheet of paper to list all the tables, variables, ranges, etc. Put in the actual values.

Pretend you are the computer and perform each step exactly as your code directs.

I think the problem will become immediately apparent.

= = = =

Failing that:

Try some direct testing on the snippet of code that your provided. You may need to add another line or two.

According to your code what data values are going in, what values are coming out, and what values do you expect to be coming out?

Show examples of what works and what does not work.

And there are other ways to determine if something already exists in a list.

Take a closer look at the many functions that are available within Excel.