Macro to Look for value in Column and stop running other macro code if found.

Z

zach9208

Board Regular
Joined
Dec 15, 2015
Messages
117
Hello. I am looking for a macro code that I can use to look down all of column "A" and check for any cell that has the word "Error". If "Error" is found, I want to stop running the remainder of the macro code and generate a pop up box warning of error. It would be awesome if there was a continue and end button on this popup box.

If the continue button was clicked it would continue running the remainder of the macro regardless of errors being present. If the end button was clicked it would close the box and return back to sheet. Below is the code I want to use depending on the if/then statement.




Code: 
Sub CreateUserInitiatedLoadCSV()Dim wbNew As Workbook
Dim wbSrc As Workbook
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range




    Set wbSrc = ThisWorkbook
    Set sh1 = wbSrc.ActiveSheet


    nmary = Array("ODS ID#", "Counterparty", "Moodys", "S&P", "Fitch", "Country of Domicile", "Ult. Parent Country of Domicile")
    Set wbNew = Workbooks.Add(xlWBATWorksheet)


    Set sh2 = wbNew.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(4).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
        sh2.Columns.AutoFit
    Next
        
   ' Store current details for the workbook
    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
    
    SaveToDirectory = "C:\Users\Zach\Desktop\"


    wbNew.SaveAs Filename:=SaveToDirectory & "CounterPartyUIL" & "-" & Format(Date, "MM-YYYY") & ".csv", FileFormat:=xlCSV
    
    wbNew.Close savechanges:=False


    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True


End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hello Zach, I think this is what you're looking for. The code below will look through all cells in Column A. If any of them have the word "Error", a Message Box appears asking the user if they want to continue. If the user clicks Yes, the code continues running. If the user selects No, the code immediately stops running. This code is in a simplified format to show the general idea of what needs to happen and will need edited (see below) to work with your code effectively.

Code: 
Sub ErrorMsg()
    Dim r As Range, KeepRunning As VbMsgBoxResult
    
    For Each r In Range("A1:A" & Range("A1").End(xlDown).Row)
        If LCase(r) = "error" Then
            KeepRunning = MsgBox("An error was found in cell " & r.Address & ". Would you like to continue?", _
                vbQuestion + vbYesNo, "Error Found")
            If KeepRunning = vbNo Then Exit Sub
        End If
    Next r
End Sub

This code is how I think it should look once it's inserted into your code. The red lines are the new ones I'm adding. However, I haven't tested this version, only the one above. Please use caution when inserting this code. Let me know if this does what you'd like it to.

Code: 
Sub CreateUserInitiatedLoadCSV()
Dim wbNew As Workbook
Dim wbSrc As Workbook
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range
[COLOR=#ff0000]Dim r As Range, KeepRunning As VbMsgBoxResult[/COLOR]






    Set wbSrc = ThisWorkbook
    Set sh1 = wbSrc.ActiveSheet


[COLOR=#ff0000]    For Each r In sh1.Range("A1:A" & sh1.Range("A1").End(xlDown).Row)[/COLOR]
[COLOR=#ff0000]        If LCase(r) = "error" Then[/COLOR]
[COLOR=#ff0000]            KeepRunning = MsgBox("An error was found in cell " & r.Address & ". Would you like to continue?", _[/COLOR]
[COLOR=#ff0000]                Buttons:=vbQuestion + vbYesNo, Title:="Error Found")[/COLOR]
[COLOR=#ff0000]            If KeepRunning = vbNo Then Exit Sub[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
[COLOR=#ff0000]    Next r[/COLOR]


    nmary = Array("ODS ID#", "Counterparty", "Moodys", "S&P", "Fitch", "Country of Domicile", "Ult. Parent Country of Domicile")
    Set wbNew = Workbooks.Add(xlWBATWorksheet)




    Set sh2 = wbNew.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(4).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
        sh2.Columns.AutoFit
    Next
        
   ' Store current details for the workbook
    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
    
    SaveToDirectory = "C:\Users\Zach\Desktop\"




    wbNew.SaveAs Filename:=SaveToDirectory & "CounterPartyUIL" & "-" & Format(Date, "MM-YYYY") & ".csv", FileFormat:=xlCSV
    
    wbNew.Close savechanges:=False




    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True




End Sub
 
Upvote 0
Veritan said:
Hello Zach, I think this is what you're looking for. The code below will look through all cells in Column A. If any of them have the word "Error", a Message Box appears asking the user if they want to continue. If the user clicks Yes, the code continues running. If the user selects No, the code immediately stops running. This code is in a simplified format to show the general idea of what needs to happen and will need edited (see below) to work with your code effectively.

Code: 
Sub ErrorMsg()
    Dim r As Range, KeepRunning As VbMsgBoxResult
    
    For Each r In Range("A1:A" & Range("A1").End(xlDown).Row)
        If LCase(r) = "error" Then
            KeepRunning = MsgBox("An error was found in cell " & r.Address & ". Would you like to continue?", _
                vbQuestion + vbYesNo, "Error Found")
            If KeepRunning = vbNo Then Exit Sub
        End If
    Next r
End Sub

This code is how I think it should look once it's inserted into your code. The red lines are the new ones I'm adding. However, I haven't tested this version, only the one above. Please use caution when inserting this code. Let me know if this does what you'd like it to.

Code: 
Sub CreateUserInitiatedLoadCSV()
Dim wbNew As Workbook
Dim wbSrc As Workbook
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range
[COLOR=#ff0000]Dim r As Range, KeepRunning As VbMsgBoxResult[/COLOR]






    Set wbSrc = ThisWorkbook
    Set sh1 = wbSrc.ActiveSheet


[COLOR=#ff0000]    For Each r In sh1.Range("A1:A" & sh1.Range("A1").End(xlDown).Row)[/COLOR]
[COLOR=#ff0000]        If LCase(r) = "error" Then[/COLOR]
[COLOR=#ff0000]            KeepRunning = MsgBox("An error was found in cell " & r.Address & ". Would you like to continue?", _[/COLOR]
[COLOR=#ff0000]                Buttons:=vbQuestion + vbYesNo, Title:="Error Found")[/COLOR]
[COLOR=#ff0000]            If KeepRunning = vbNo Then Exit Sub[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
[COLOR=#ff0000]    Next r[/COLOR]


    nmary = Array("ODS ID#", "Counterparty", "Moodys", "S&P", "Fitch", "Country of Domicile", "Ult. Parent Country of Domicile")
    Set wbNew = Workbooks.Add(xlWBATWorksheet)




    Set sh2 = wbNew.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(4).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
        sh2.Columns.AutoFit
    Next
        
   ' Store current details for the workbook
    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
    
    SaveToDirectory = "C:\Users\Zach\Desktop\"




    wbNew.SaveAs Filename:=SaveToDirectory & "CounterPartyUIL" & "-" & Format(Date, "MM-YYYY") & ".csv", FileFormat:=xlCSV
    
    wbNew.Close savechanges:=False




    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True




End Sub
Click to expand...

Thanks for the reply. I was not able to get this to work. I used the step thru function and couldn't figure out what was going wrong...Below is additional info that might help. Thanks again!

Additional Info:
Data in column A starts in cell A4. I am using an if statement that is returning "Error" as soon as logic is not met. It should be populating the message box since I have several instances where this formula is returning "Error". In case you did not notice, the existing macro is grabbing the columns that I need from my source document and moving to a new workbook. I want this copying process to not initiate if there are errors. The "Yes" button on the macro will allow the user to override if the error cannot/shouldn't be resolved. Hope this additional info helps. Thanks!!!!
 
Upvote 0
I've got a couple questions that may help with this, if you don't mind. First, the data that is in column A starting at A4, I'm assuming this is in your source document, is that correct? Second, do you want to check for "Error" in a column by column process? In other words, let's assume that A10 has returned "Error". If the user selects to continue the code by clicking Yes, the code will copy the ODS ID# column over. Then the code checks for errors again, again noticing that A10 has "Error". The user selects Yes again, and the code copies the Counterparty column. Finally, when asked for the third time if the user wishes to continue, the user selects No. The code exits the sub and does not continue copying any more of the columns. Is this a valid example of a scenario you might run into? If not, can you provide a better example showing exactly how you want the copy/don't copy process to run? Finally, how do you want to handle multiple errors in column A? Do you want the code to only check for the first occurrence of the word "Error", ask the user a single time if they want to continue copying, and copy or don't copy according to the user's input? Or do you want the code to ask the user once for every occurrence of the word "Error" in column A? If so, what should the appropriate response be for both Yes and No responses from the user when there are multiple Errors in column A? Thanks for the help, it makes it a lot easier to figure out how to get the code to work.
 
Upvote 0
Veritan said:
I've got a couple questions that may help with this, if you don't mind. First, the data that is in column A starting at A4, I'm assuming this is in your source document, is that correct? Second, do you want to check for "Error" in a column by column process? In other words, let's assume that A10 has returned "Error". If the user selects to continue the code by clicking Yes, the code will copy the ODS ID# column over. Then the code checks for errors again, again noticing that A10 has "Error". The user selects Yes again, and the code copies the Counterparty column. Finally, when asked for the third time if the user wishes to continue, the user selects No. The code exits the sub and does not continue copying any more of the columns. Is this a valid example of a scenario you might run into? If not, can you provide a better example showing exactly how you want the copy/don't copy process to run? Finally, how do you want to handle multiple errors in column A? Do you want the code to only check for the first occurrence of the word "Error", ask the user a single time if they want to continue copying, and copy or don't copy according to the user's input? Or do you want the code to ask the user once for every occurrence of the word "Error" in column A? If so, what should the appropriate response be for both Yes and No responses from the user when there are multiple Errors in column A? Thanks for the help, it makes it a lot easier to figure out how to get the code to work.
Click to expand...


1)Correct the source document starts having data in A4.
2)I only want to check column A for this error. If there is any error present down this column I want this popup box to populate. In some cases there are multiple cells with "Error" down this column. If there is even 1 error or even 10+ I want this popup box and to stop the macro until the user chooses to continue with the errors or stop and fix an rerun the macro again.
3) I want to make the user aware of any errors. Some errors will be acceptable and others not. I just want it to pop up to draw attention for them to review. In other words, even if there is one error, if they click No then the entire macro stops cold and does not create the new workbook. I want the yes, in case they want to override the check without having to input any data to fix the if statement from showing error when an error is acceptable. It is essentially a data integrity thing. So again if any error is detected, I want to point out there was atleast one error detected and they can either proceed or end the macro to fix any errors.
4) Popup box would be used like this: There was atleast one error detected for either ODS ID or Country. Please review the data. If data is correct click "proceed" otherwise press "cancel" to review and correct errors and rerun macro again.
 
Last edited:
Upvote 0
You can try this code and see if it works. If it doesn't, could you provide more information on what you mean when you said you were not able to get the code to work? I created a sheet with similar characteristics to yours and ran the code I first put in here and it worked right away, so I'm not sure what aspect of it is not working properly for you. The code below is slightly different, but in effect it is very similar to the above. This will look in Column A in your source sheet. If it finds an instance of the word "Error", the message box will initialize. If the user clicks Yes, the rest of the code will execute without incident. If the user clicks No, the code will immediately stop running. It will only ask one time if the user wants to continue, and then will either copy all columns or cease running, depending on what the user responds with.

Code: 
Sub CreateUserInitiatedLoadCSV()
[COLOR=#ff0000]Dim wbNew As Workbook, wbSrc As Workbook, Error As Range[/COLOR]
[COLOR=#ff0000]Dim SaveToDirectory$, CurrentWorkbook$, KeepRunning As VbMsgBoxResult[/COLOR]
[COLOR=#ff0000]Dim CurrentFormat&, nmary, sh1 As Worksheet, sh2 As Worksheet, i&, rng As Range[/COLOR]


    Set wbSrc = ThisWorkbook
    Set sh1 = wbSrc.ActiveSheet
[COLOR=#ff0000]    Set Error = sh1.Range("A4:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Find("Error", LookIn:=xlValues, LookAt:=xlWhole)[/COLOR]
[COLOR=#ff0000]    If Not Error Is Nothing Then[/COLOR]
[COLOR=#ff0000]        KeepRunning = MsgBox("One or more errors were detected. Would you like to continue anyways?", _[/COLOR]
[COLOR=#ff0000]            Buttons:=vbExclamation + vbYesNo + vbDefaultButton2, Title:="Error Found")[/COLOR]
[COLOR=#ff0000]        If KeepRunning = vbNo Then Exit Sub[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]


    nmary = Array("ODS ID#", "Counterparty", "Moodys", "S&P", "Fitch", "Country of Domicile", "Ult. Parent Country of Domicile")
    Set wbNew = Workbooks.Add


    Set sh2 = wbNew.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(4).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
        sh2.Columns.AutoFit
    Next
    
   ' Store current details for the workbook
    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
    
    SaveToDirectory = "C:\Users\Zach\Desktop\"
    wbNew.SaveAs Filename:=SaveToDirectory & "CounterPartyUIL" & "-" & Format(Date, "MM-YYYY") & ".csv", FileFormat:=xlCSV
    wbNew.Close savechanges:=False


    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
Veritan said:
You can try this code and see if it works. If it doesn't, could you provide more information on what you mean when you said you were not able to get the code to work? I created a sheet with similar characteristics to yours and ran the code I first put in here and it worked right away, so I'm not sure what aspect of it is not working properly for you. The code below is slightly different, but in effect it is very similar to the above. This will look in Column A in your source sheet. If it finds an instance of the word "Error", the message box will initialize. If the user clicks Yes, the rest of the code will execute without incident. If the user clicks No, the code will immediately stop running. It will only ask one time if the user wants to continue, and then will either copy all columns or cease running, depending on what the user responds with.

Code: 
Sub CreateUserInitiatedLoadCSV()
[COLOR=#ff0000]Dim wbNew As Workbook, wbSrc As Workbook, Error As Range[/COLOR]
[COLOR=#ff0000]Dim SaveToDirectory$, CurrentWorkbook$, KeepRunning As VbMsgBoxResult[/COLOR]
[COLOR=#ff0000]Dim CurrentFormat&, nmary, sh1 As Worksheet, sh2 As Worksheet, i&, rng As Range[/COLOR]


    Set wbSrc = ThisWorkbook
    Set sh1 = wbSrc.ActiveSheet
[COLOR=#ff0000]    Set Error = sh1.Range("A4:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Find("Error", LookIn:=xlValues, LookAt:=xlWhole)[/COLOR]
[COLOR=#ff0000]    If Not Error Is Nothing Then[/COLOR]
[COLOR=#ff0000]        KeepRunning = MsgBox("One or more errors were detected. Would you like to continue anyways?", _[/COLOR]
[COLOR=#ff0000]            Buttons:=vbExclamation + vbYesNo + vbDefaultButton2, Title:="Error Found")[/COLOR]
[COLOR=#ff0000]        If KeepRunning = vbNo Then Exit Sub[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]


    nmary = Array("ODS ID#", "Counterparty", "Moodys", "S&P", "Fitch", "Country of Domicile", "Ult. Parent Country of Domicile")
    Set wbNew = Workbooks.Add


    Set sh2 = wbNew.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(4).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
        sh2.Columns.AutoFit
    Next
    
   ' Store current details for the workbook
    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
    
    SaveToDirectory = "C:\Users\Zach\Desktop\"
    wbNew.SaveAs Filename:=SaveToDirectory & "CounterPartyUIL" & "-" & Format(Date, "MM-YYYY") & ".csv", FileFormat:=xlCSV
    wbNew.Close savechanges:=False


    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True
End Sub
Click to expand...

Worked perfectly!!! Thanks so much for your time and help. It is much appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Macro to Copy Entitre column of Matching Column Header & Paste to Matching Column Header
Replies
0
Views
2K
zach9208
Z
J
  • Question Question
Macro to run as long as there is a value in column A
Replies
2
Views
1K
jenowell
J

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top