Consulting

Results 1 to 5 of 5

Thread: Solved: Macro To Loop through a column in the workbook

  1. #1
    VBAX Newbie
    Joined
    Feb 2010
    Posts
    3
    Location

    Solved: Macro To Loop through a column in the workbook

    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

  2. #2
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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]

  3. #3
    VBAX Newbie
    Joined
    Feb 2010
    Posts
    3
    Location

    Smile

    The macro works like a charm. Thanks so much.

    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]

  4. #4
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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]

  5. #5
    VBAX Newbie
    Joined
    Feb 2010
    Posts
    3
    Location

    Talking

    Thanks ! Worked perfectly.

    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •