How to do in place increments in VBA?-CodePudding

I am trying to use a VBA for loop in Word to loop through the rows of a table. The column in constant and is set to column four of the table. If a particular cell in the given row has the text "Yes", I want to add 1 to X which I initiated as 0 at the start of the code. My code does not do the increment and keeps returning a zero or any other value that I initiate X to be. Attached below is my current VBA code.

Sub Row_Iter()

Dim otable As Table
Set otable = ActiveDocument.Tables(1)
Dim Row As Integer
Dim Col As Integer
Col = 4
Dim x As Integer

''Section 1

With otable.Columns(Col)
    x = 0
    For Row = 4 To Row = 7
        If otable.Cell(Row, Col).Range.Text = "Yes" Then
                x = x   1
        End If
    Next Row
End With

End Sub

CodePudding user response：

As @rnold_CK said in the comments: The task can be solved by the excel formula countif.

In the VDA code the line

For Row = 4 To Row = 7

has an error: The 2nd "Row =" has to be removed.

Sub Row_Iter()

Dim otable ' As Table
Set otable = Sheets(1) '.Sheet(1)
Dim Row As Integer
Dim Col As Integer
Col = 4
Dim x As Integer

''Section 1

With otable.Columns(Col)
    x = 0
    For Row = 4 To 7
        If otable.Cells(Row, Col).Text = "Yes" Then
                x = x   1
        End If
    Next Row
End With

MsgBox "count yes: " & x

End Sub

CodePudding user response：

There are two problems with your code.

The first is that For Row = 4 To Row = 7 should be For Row = 4 To 7

The second is that If otable.Cells(Row, Col).Range.Text = "Yes" will never evaluate to True. This is because each cell contains a non-printing end of cell marker which consists of two characters. You can see this quite clearly by checking the length of an empty cell in the Immediate window, e.g.

?len(ActiveDocument.Tables(1).Cell(1,1).Range.Text)

So, your code should be:

Sub Row_Iter()

Dim otable As Table
Set otable = ActiveDocument.Tables(1)
Dim Row As Integer
Dim Col As Integer
Col = 4
Dim x As Integer

    x = 0
    For Row = 4 To 7
        If left(otable.Cell(Row, Col).Range.Text, 3) = "Yes" Then
            x = x   1
        End If
    Next Row

End Sub