Check for "x" and return notification of where "x" was found

[pgrad]

Hi All,

I would like to write some VBA to check a column of 2000 rows for the number 3.

If 3 is found then I would like a msg box to appear telling the user what row it is on and selecting that row.

If more than one instance of 3 is found then I would like this to repeat or loop until all corrections have been made.

So far all I have is:

If Range("AO9:AO2031") = 3 Then
            MsgBox " Please ensure you have no incomplete rows"
            Else

Any help or alternative soloutions much appreciated

Thanks
Paul

 

[JazzSP8]

Hi Paul

I've just adapted this to your needs, it will check the range you specified and give you a message box each time a 3 is found in the cell and let you know what number row it was.

You can adapt it from here?

Sub Three()
For i = 9 To 2031
    If Cells(i, "AO").Value = 3 Then
        MsgBox "Please ensure you have no incomplete rows, check Row " & i
    End If
Next i
End Sub

If you need anything else or help amending then shout up

 

[pgrad]

Perfect!

Would you mind explaining what "i" is doing?

Thanks
Paul

 

[JazzSP8]

Hi Paul

Sorry for not getting back to you sooner, had some, uhm, 'fun' last night

Annnnnnnnnnnnyways.

i is taking the place of your row number, when using the FOR it's saying you want to count from 9 to 2031, the range of rows, once the row number associated to i has been checked, move onto the NEXT one.

So, check starting from row 9, IF the condition has been met (check for a 3) THEN display a message box, if it hasn't move onto the next row until row 2031 is reached.

Because i has been associated with a row number you can also use it on the message box to display that row number as you would normal text.

Hope that makes sense for you

 

[pgrad]

It makes sense in theory...

I guess the acid test is next time I need to apply it, but I have a good starting point now!

Thanks and have a great weekend!

 

[pgrad]

Jazz sorry to be a pain,

The code is working well..... but then saving the workbook even though there are errors present.

Im not entirely sure how to stop the save bit kicking in if there are errors.

Do I need to put an else in somewhere?

    Dim x As Integer
For x = 1 To Sheets.Count
Sheets(x).Unprotect Password:="abc"
Next x
'This will check name, role and locality have been input and return a msg box if they are blank
If Range("B1") = "" Then
Range("B1").Value = Application.InputBox("Please enter your full name")
Else
    If Range("B2") = "" Then
    MsgBox "Please select your role!"
    Else
        If Range("B3") = "" Then
        MsgBox "Please select your locality!"
        Else
        
        Calculate
'This will check for "Please select to left" and return an error msg if any exist
            If Range("AK2") >= 1 Then
            MsgBox " Please ensure you have no incomplete rows"
            Else
            
'This will check the figures in column AP, if any are equal to or greater than 0 it will return an error msg informing which row contains a value of three
                For i = 9 To 231
                If Cells(i, "AP").Value <> 0 Then
                MsgBox "Please ensure you have no incomplete rows, check Row " & i
        
                Calculate
        
                End If
Next i
            
'This will enable all Command bars
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = True
    Next
    
'This saves the file in the specified file location with the filename specified in cell AA2
ActiveWorkbook.SaveAs Filename:="G:\BID\SWOT\Adult Service Redesign\ABC Pilot\Data\" + Range("AA2"), FileFormat:=xlWorkbookNormal
'This closes the workbook
ActiveWorkbook.Close False
End If
    End If
        End If
            End If
End Sub

Thanks
Paul

 

[JazzSP8]

No worries Paul

The easiest way would be to exit out at the first sign of a problem, this will bring up the message informing the user to check the row and then stop processing anything else, so they would be forced to amend the row and then start the process over again.

Would that be OK?

    Dim x As Integer
For x = 1 To Sheets.Count
Sheets(x).Unprotect Password:="abc"
Next x
'This will check name, role and locality have been input and return a msg box if they are blank
If Range("B1") = "" Then
Range("B1").Value = Application.InputBox("Please enter your full name")
Else
    If Range("B2") = "" Then
    MsgBox "Please select your role!"
    Else
        If Range("B3") = "" Then
        MsgBox "Please select your locality!"
        Else
        
        Calculate
'This will check for "Please select to left" and return an error msg if any exist
            If Range("AK2") >= 1 Then
            MsgBox " Please ensure you have no incomplete rows"
            Else
            
'This will check the figures in column AP, if any are equal to or greater than 0 it will return an error msg informing which row contains a value of three
                For i = 9 To 231
                If Cells(i, "AP").Value <> 0 Then
                MsgBox "Please ensure you have no incomplete rows, check Row " & i
        
                Calculate
                Exit Sub
        
                End If
Next i
            
'This will enable all Command bars
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = True
    Next
    
'This saves the file in the specified file location with the filename specified in cell AA2
ActiveWorkbook.SaveAs Filename:="G:\BID\SWOT\Adult Service Redesign\ABC Pilot\Data\" + Range("AA2"), FileFormat:=xlWorkbookNormal
'This closes the workbook
ActiveWorkbook.Close False
End If
    End If
        End If
            End If
End Sub

 

[pgrad]

Perfect!

But this has highlighted an oversight on my part which I should have raised earlier. Apologies.

I need to ignore a few rows as these have headings in them.

Is this possible if I play about with

For i = 9 To 231

Thanks
Paul

 

[JazzSP8]

Yeah - Assuming the headings aren't in the middle of the data, just change the 9 or 231 to a row that's better as a start or an end point.

If your headings are in the middle of your data then you'll need to change the for loop to check for the heading so it can be ignored, something like;

For i = 9 To 231

    If Cells(i, "AP").Value = HEADING Then GoTo Skip

    If Cells(i, "AP").Value <> 0 Then
        MsgBox "Please ensure you have no incomplete rows, check Row " & i
        Calculate
        Exit Sub
    End If

Skip:

Next i

Change the HEADING to something relevant

(Assuming that this is part of the code your talking about of course )

 

[pgrad]

I have got this so far:

'This will check the figures in column AP, if any are equal to or greater than 0 it will return an error msg informing which row contains a value of three
                For i = 9 To 231
                If Cells(i, "AP").Value = "Monday" Then GoTo Skip
                If Cells(i, "AP").Value = "Tuesday" Then GoTo Skip
                If Cells(i, "AP").Value = "Wednesday" Then GoTo Skip
                If Cells(i, "AP").Value = "Thursday" Then GoTo Skip
                If Cells(i, "AP").Value = "Friday" Then GoTo Skip
                If Cells(i, "AP").Value = "Saturday" Then GoTo Skip
                If Cells(i, "AP").Value = "Sunday" Then GoTo Skip
                If Cells(i, "AP").Value <> 0 Then
                MsgBox "Please ensure you have no incomplete rows, check Row " & i
        
                Calculate
                Exit Sub
        
                End If
Next i
            
'This will enable all Command bars
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = True
    Next
    
'This saves the file in the specified file location with the filename specified in cell AA2
ActiveWorkbook.SaveAs Filename:="G:\BID\SWOT\Adult Service Redesign\ABC Pilot\Data\" + Range("AA2"), FileFormat:=xlWorkbookNormal
'This closes the workbook
ActiveWorkbook.Close False
End If
    End If
        End If
            End If
End Sub

I'm getting a compile error on the goto skip bit, "Label not defined", can you tell me what I've done wrong?

Thanks so much!