Solved: Macro To Loop through a column in the workbook

:motz2: I'm trying to write a macro which will start the loop from cell M8 in my workbook. Essentially looping through all the sheets except "test".

The basic idea of the loop is to check whether any cells have a value of 1 in col M beginning from row 8 down to the last used cell and if it does find 1, then to look across the corresponding row in Col B to ascertain whether it is blank or not.

Example, say cell M8 = 1 then macro looks at Cell B8, and if cell B8 = blank.

If in the workbook after looping through all the sheets except "test", if the macro finds cells in col B = Blank when the corresponding row in Col M = 1, then it should give the excel user a Msg to check for missing explanations in col b.

I think this code should run in "workbook" module as a Workbook Before_CLose event.

I've the basic idea but don't know how to go about constructing the code.

Any help will be appreciated.

Thanks,

K

Try this one:
[VBA]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iSht As Integer, lRow As Long, strOut As String, i As Long, shtCur As Worksheet
For iSht = 1 To Sheets.Count
Set shtCur = Sheets(iSht)
If Ucase(shtCur.Name) <> "TEST" Then
lRow = shtCur.Cells(Rows.Count, 13).End(xlUp).Row
For i = 8 To lRow
If shtCur.Cells(i, 13) = 1 And shtCur.Cells(i, 2) = "" Then
strOut = strOut & shtCur.Name & " row number " & i & vbLf
End If
Next
End If
Next

If strOut <> "" Then
MsgBox "Please complete the following" & vbLf & strOut, vbOKOnly + vbSystemModal
Cancel = True

End If
End Sub[/VBA]

The macro works like a charm. Thanks so much.:friends:

I just wanted to make one small change, currently as it executes..once it finds that there are rows in col B which need to be populated, macro will not allow to close the workbook till those are populated.

Can it be revised so as to give me the choice such that I can still close the workbook without making the changes..

Thanks again for your help.

Quote:

---

Originally Posted by mbarron

Try this one:
[vba]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iSht As Integer, lRow As Long, strOut As String, i As Long, shtCur As Worksheet
For iSht = 1 To Sheets.Count
Set shtCur = Sheets(iSht)
If Ucase(shtCur.Name) <> "TEST" Then
lRow = shtCur.Cells(Rows.Count, 13).End(xlUp).Row
For i = 8 To lRow
If shtCur.Cells(i, 13) = 1 And shtCur.Cells(i, 2) = "" Then
strOut = strOut & shtCur.Name & " row number " & i & vbLf
End If
Next
End If
Next

If strOut <> "" Then
MsgBox "Please complete the following" & vbLf & strOut, vbOKOnly + vbSystemModal
Cancel = True

End If
End Sub[/vba]

---

Here you go.
[VBA]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iSht As Integer, lRow As Long, strOut As String, i As Long, shtCur As Worksheet
Dim closeIt
For iSht = 1 To Sheets.Count
Set shtCur = Sheets(iSht)
If UCase(shtCur.Name) <> "TEST" Then
lRow = shtCur.Cells(Rows.Count, 13).End(xlUp).Row
For i = 8 To lRow
If shtCur.Cells(i, 13) = 1 And shtCur.Cells(i, 2) = "" Then
strOut = strOut & shtCur.Name & " row number " & i & vbLf
End If
Next
End If
Next

If strOut <> "" Then
closeIt = MsgBox("The following are not complete" & vbLf & strOut _
& "Do you want to exit without entering the daat" & vbLf & _
"Click Yes to exit, click No to edit", vbYesNo)
If closeIt = vbYes Then
Cancel = False
Else
Cancel = True
End If
End If
End Sub[/VBA]

Thanks ! Worked perfectly. :rotlaugh:

Quote:

---

Originally Posted by mbarron

Here you go.
[vba]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iSht As Integer, lRow As Long, strOut As String, i As Long, shtCur As Worksheet
Dim closeIt
For iSht = 1 To Sheets.Count
Set shtCur = Sheets(iSht)
If UCase(shtCur.Name) <> "TEST" Then
lRow = shtCur.Cells(Rows.Count, 13).End(xlUp).Row
For i = 8 To lRow
If shtCur.Cells(i, 13) = 1 And shtCur.Cells(i, 2) = "" Then
strOut = strOut & shtCur.Name & " row number " & i & vbLf
End If
Next
End If
Next

If strOut <> "" Then
closeIt = MsgBox("The following are not complete" & vbLf & strOut _
& "Do you want to exit without entering the daat" & vbLf & _
"Click Yes to exit, click No to edit", vbYesNo)
If closeIt = vbYes Then
Cancel = False
Else
Cancel = True
End If
End If
End Sub[/vba]

---