MS Word VBA, Len() working on first table cell while not working on second one-CodePudding

I am trying to do some automated find and replace in some parts of the text. The first one works properly while the second one is returning an error, the issue is on the Len() function if I understand correctly.

Sub FindReplace()

Dim aTable As Table
Dim aCell, bCell As Cell

Application.DisplayAlerts = False

For Each aTable In ActiveDocument.Tables
Set aCell = aTable.Cell(1, 2)
Set bCell = aTable.Cell(2, 2)


    With Selection.Find
        .Text = "[Êïõôß 1]"
        .Replacement.Text = Trim(Left(aCell, Len(aCell) - 2))
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
    With Selection.Find
        .Text = "[Êïõôß 2]"
        .Replacement.Text = Trim(Left(bCell, Len(bCell) - 2))
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

Next aTable

Application.DisplayAlerts = True

End Sub

Please for your help!

CodePudding user response：

The error is within your variable declaration:

Dim aCell, bCell As Cell

aCell is dimmed implicitly as Variant. Therefore Len can handle it by taking the default property of Cell

But bCell is dimmed as Cell which can't be passed to Len

Solution: Be explicit in your dimming:

Dim aCell As Cell, bCell As Cell

And use Len(aCell.Range.Text) and Len(bCell.Range.Text) to retrieve the content of the cell.