Excel VBA。对于范围中的每一行，对于行中的每个单元格，对于单元格中的每个字符

Excel VBA. For each row in range, for each cell in row, for each character in cell

提问by Davids Gaisevskis

I have created a VBA macro simple to absurdity.

我创建了一个简单到荒谬的 VBA 宏。

For each row in range, then for each cell in row and then for each character in cell.

对于范围中的每一行，然后是行中的每个单元格，然后是单元格中的每个字符。

But it returns property or method not supported.

但它返回不受支持的属性或方法。

Now, to refer to each character in a cell in Excel VBA, it has to be defined as a range. So how is not a cell in a row in a range not a range object?

现在，要在 Excel VBA 中引用单元格中的每个字符，必须将其定义为一个范围。那么如何不是范围对象中的一行中的单元格呢？

In all Excel examples the cells with Characters property have been referred to in absolute values. Like Worksheets("Sheet1").Range("A1")

在所有 Excel 示例中，具有 Characters 属性的单元格都以绝对值引用。喜欢Worksheets("Sheet1").Range("A1")

Do I have to write a code of Worksheets("MySheet").Range("B1")... Worksheets("MySheet").Range("B2")... ... Worksheets("MySheet").Range("B250")To access characters or is there a way to access each character in a cell without Mid function?

我必须写的代码 Worksheets("MySheet").Range("B1")... ... Worksheets("MySheet").Range("B2")... ... ... ... Worksheets("MySheet").Range("B250")要访问字符或有办法进入小区中的每个字符没有Mid函数？

By the way, Mid function looses formatting but I am looking for underlined characters.

顺便说一句， Mid 函数会丢失格式，但我正在寻找带下划线的字符。

My program is like this

我的程序是这样的

Sub Celltest()
    For Each rw In Sheets("Joblist").Range("B1:D250").Rows
        For Each cel In rw.Cells
            For Each char In cel.Characters
                If char.Font.Underline = True Then MsgBox char
            Next
        Next
    Next
End Sub

As a result I get message that a property or method is not supported.

结果，我收到不支持属性或方法的消息。

Thank you!

谢谢！

David

大卫

回答by BruceWayne

"Why is suddenly a part of a range object suddenly not a range?" That's not the issue. The error is Object doesn't support this property or method. That means that (basically) anything after the .is an object, so the error is telling you that somethingis up with how you're using .Character.

“为什么突然范围对象的一部分突然不是范围？” 这不是问题。错误是Object doesn't support this property or method。这意味着，后（基本）什么.是一个对象，因此错误是告诉你，什么是与您是如何使用.Character。

I did some quick searching around, and this works:

我做了一些快速搜索，这有效：

Sub Celltest2()
Dim rw As Range, cel As Range
Dim i As Integer
Dim char    
    For Each rw In Sheets("Joblist").Range("B1:D250").Rows
        For Each cel In rw.Cells
            For i = 1 To Len(cel)
                'Debug.Print cel.Characters(i, 1).Text
                If cel.Characters(i, 1).Font.Underline = 2 Then
                    MsgBox (cel.Characters(i, 1).Text)
                End If
            Next i
        Next
    Next
End Sub

I just looked up the Characters Objectdocumentation, and there is the answer of how to use .Characters. The issue is how .Charactersis used with the range - no sophisticated skills or knowledge necessary. Just use the VB Error messages.

我刚刚查了一下Characters Object文档，里面有如何使用.Characters. 问题是如何.Characters与范围一起使用 - 不需要复杂的技能或知识。只需使用 VB 错误消息。

回答by xidgel

Two things I noticed. 1. Characters is not a collection, so For Each won't work. 2. Font.Underline is not Boolean. Try something like:

我注意到两件事。1. Characters 不是集合，因此 For Each 将不起作用。2. Font.Underline 不是布尔值。尝试类似：

Sub Celltest()
    Dim rw As Excel.Range
    Dim cel As Excel.Range
    Dim char As Excel.Characters
    Dim I As Long
    For Each rw In Sheets("Joblist").Range("B1:D250").Rows
        For Each cel In rw.Cells
            For I = 1 To cel.Characters.Count
                Set char = cel.Characters(I, 1)
                If char.Font.Underline <> xlUnderlineStyleNone Then MsgBox char.Text
            Next I
        Next
    Next
    Set rw = Nothing
    Set cel = Nothing
    Set char = Nothing
End Sub

Hope that helps

希望有帮助