CODEWITHSUNDEEP
vbaexcelexcel-2010
atame
atame

Reputation: 521

VBA Loop through sheet and check accuracy

I am trying to get a loop to work.

What i need to happen is to loop over a sheet and if address matches in column A does column W have a value, if all instances of address have values in column W then GoTo end_nothing. If only some matches have data in column W then continue with sub.

The problem I am having with my code is that the first entry it matches, it exits the sub without looping over the rest.

My Code:

Private Sub CommandButton3_Click()
Dim add_WIR_check, lastRow_WIR As Long
Dim address_Check  As String: address_Check = sheets("Auto Checklist").cells(3, 2).value

lastRow_WIR = sheets("Works Instruction Record").cells(Rows.count, "A").End(xlUp).Row

***** Unprotect Sheet

For add_WIR_check = 3 To lastRow_WIR
If sheets("Works Instruction Record").cells(add_WIR_check, 1) = address_Check Then
    If sheets("Works Instruction Record").cells(add_WIR_check, 23) <> "" Then
        GoTo end_nothing
    End If
End If
Next add_WIR_check

***** Additional code here

end_nothing:
***** Protect sheet
End Sub

Upvotes: 1

Views: 140

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

Your VBA code snippet does not comply with the business logic as described: you must loop through all matching rows in the range to find out if all cells in the Column "W" contain value. It may be achieved by using auxiliary Boolean var ok, like shown below:

Dim ok as boolean
ok = True
For add_WIR_check = 3 To lastRow_WIR
If sheets("Works Instruction Record").cells(add_WIR_check, 1) = address_Check Then
    If Not (sheets("Works Instruction Record").cells(add_WIR_check, 23) <> "") Then
        ok = False
End If
End If
Next add_WIR_check

Upon completion, the var ok value will indicate if all cells in Column "W" of the matching rows contain values.

Hope this may help.

Upvotes: 1

Related Questions