Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro - message box if find nothing
Hi All,
I currently have a huge macro that I use to import .asc files into excel for validation, and other things. This macro separates project accounts from non project accounts and performs a series of tasks on the project accounts. The project accounts can be differentiated from non project accounts because they all have an "X" in them... So, in my code, I first have to import the delimited data, set up column breaks, and then sort by my account column to get all of the account data at the bottom of the wkst...(code is already written for this, and I have been working on it for a month, so I don't want to change it). With that said, I have my macro find "X" and then put a page break between all the project accounts, and the non project accounts so I can then do all my tasks with project accounts... So, now that I explained some stuff....here is the problem (question)... If the macro does not find any "x" in column C, then it will pop up an error saying macro can't run...etc...etc.... What I would like for it to do is pop up a message box that says " No Project Accounts Found", and then have the macro stop there, because all other tasks past that point are for the projects. Here is what part of my code looks like: (lots of stuff before this...vlookup tables, formatting,etc) Sheets("Modified").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Find(What:="x").EntireRow.Insert Selection.End(xlDown).Select Selection.End(xlDown).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Any suggestions on how I can get it to put that message box in?????? Thanks in advance, and sorry for being wordy.... Sara |
#2
|
|||
|
|||
This illustrates one way to handle the error:
Sub a() On Error GoTo EndThis Columns("C:C").Find(What:="x").EntireRow.Insert MsgBox """x"" found" Exit Sub EndThis: MsgBox """x"" not found" End Sub -- Jim "Frantic Excel-er" wrote in message ... | Hi All, | | I currently have a huge macro that I use to import .asc files into excel for | validation, and other things. This macro separates project accounts from non | project accounts and performs a series of tasks on the project accounts. The | project accounts can be differentiated from non project accounts because they | all have an "X" in them... | | So, in my code, I first have to import the delimited data, set up column | breaks, and then sort by my account column to get all of the account data at | the bottom of the wkst...(code is already written for this, and I have been | working on it for a month, so I don't want to change it). With that said, I | have my macro find "X" and then put a page break between all the project | accounts, and the non project accounts so I can then do all my tasks with | project accounts... | | So, now that I explained some stuff....here is the problem (question)... | If the macro does not find any "x" in column C, then it will pop up an error | saying macro can't run...etc...etc.... | | What I would like for it to do is pop up a message box that says " No | Project Accounts Found", and then have the macro stop there, because all | other tasks past that point are for the projects. | | Here is what part of my code looks like: | | (lots of stuff before this...vlookup tables, formatting,etc) | | Sheets("Modified").Select | Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | Columns("C:C").Find(What:="x").EntireRow.Insert | Selection.End(xlDown).Select | Selection.End(xlDown).Select | Range(Selection, Selection.End(xlDown)).Select | Range(Selection, Selection.End(xlToRight)).Select | Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, Header:=xlGuess, _ | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | Any suggestions on how I can get it to put that message box in?????? | | Thanks in advance, and sorry for being wordy.... | | Sara |
#3
|
|||
|
|||
Jim...
Thanks for the reply... However, I cannot figure out how to incorporate this macro into the existing macro that I already have.... I tried to just take out the "sub" and "end sub" parts, but then it doesn't work right. Also, I only need a msg box if it finds no "x", not if it does. Any more ideas? "Jim Rech" wrote: This illustrates one way to handle the error: Sub a() On Error GoTo EndThis Columns("C:C").Find(What:="x").EntireRow.Insert MsgBox """x"" found" Exit Sub EndThis: MsgBox """x"" not found" End Sub -- Jim "Frantic Excel-er" wrote in message ... | Hi All, | | I currently have a huge macro that I use to import .asc files into excel for | validation, and other things. This macro separates project accounts from non | project accounts and performs a series of tasks on the project accounts. The | project accounts can be differentiated from non project accounts because they | all have an "X" in them... | | So, in my code, I first have to import the delimited data, set up column | breaks, and then sort by my account column to get all of the account data at | the bottom of the wkst...(code is already written for this, and I have been | working on it for a month, so I don't want to change it). With that said, I | have my macro find "X" and then put a page break between all the project | accounts, and the non project accounts so I can then do all my tasks with | project accounts... | | So, now that I explained some stuff....here is the problem (question)... | If the macro does not find any "x" in column C, then it will pop up an error | saying macro can't run...etc...etc.... | | What I would like for it to do is pop up a message box that says " No | Project Accounts Found", and then have the macro stop there, because all | other tasks past that point are for the projects. | | Here is what part of my code looks like: | | (lots of stuff before this...vlookup tables, formatting,etc) | | Sheets("Modified").Select | Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | Columns("C:C").Find(What:="x").EntireRow.Insert | Selection.End(xlDown).Select | Selection.End(xlDown).Select | Range(Selection, Selection.End(xlDown)).Select | Range(Selection, Selection.End(xlToRight)).Select | Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, Header:=xlGuess, _ | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | Any suggestions on how I can get it to put that message box in?????? | | Thanks in advance, and sorry for being wordy.... | | Sara |
#4
|
|||
|
|||
I don't have your whole sub so I can't rewrite it. However, I think you'd
learn more and be much better off if you tried to understand what I gave you and applied it to your sub. That was really my point - showing how to handle the error raised by Find failing. -- Jim "Frantic Excel-er" wrote in message ... | Jim... | | Thanks for the reply... | | However, I cannot figure out how to incorporate this macro into the existing | macro that I already have.... | | I tried to just take out the "sub" and "end sub" parts, but then it doesn't | work right. Also, I only need a msg box if it finds no "x", not if it does. | | Any more ideas? | | "Jim Rech" wrote: | | This illustrates one way to handle the error: | | Sub a() | On Error GoTo EndThis | Columns("C:C").Find(What:="x").EntireRow.Insert | MsgBox """x"" found" | Exit Sub | EndThis: | MsgBox """x"" not found" | End Sub | | | -- | Jim | "Frantic Excel-er" wrote in | message ... | | Hi All, | | | | I currently have a huge macro that I use to import .asc files into excel | for | | validation, and other things. This macro separates project accounts from | non | | project accounts and performs a series of tasks on the project accounts. | The | | project accounts can be differentiated from non project accounts because | they | | all have an "X" in them... | | | | So, in my code, I first have to import the delimited data, set up column | | breaks, and then sort by my account column to get all of the account data | at | | the bottom of the wkst...(code is already written for this, and I have | been | | working on it for a month, so I don't want to change it). With that said, | I | | have my macro find "X" and then put a page break between all the project | | accounts, and the non project accounts so I can then do all my tasks with | | project accounts... | | | | So, now that I explained some stuff....here is the problem (question)... | | If the macro does not find any "x" in column C, then it will pop up an | error | | saying macro can't run...etc...etc.... | | | | What I would like for it to do is pop up a message box that says " No | | Project Accounts Found", and then have the macro stop there, because all | | other tasks past that point are for the projects. | | | | Here is what part of my code looks like: | | | | (lots of stuff before this...vlookup tables, formatting,etc) | | | | Sheets("Modified").Select | | Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, | _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | Columns("C:C").Find(What:="x").EntireRow.Insert | | Selection.End(xlDown).Select | | Selection.End(xlDown).Select | | Range(Selection, Selection.End(xlDown)).Select | | Range(Selection, Selection.End(xlToRight)).Select | | Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, | Header:=xlGuess, _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | | | Any suggestions on how I can get it to put that message box in?????? | | | | Thanks in advance, and sorry for being wordy.... | | | | Sara | | | |
#5
|
|||
|
|||
Jim...
I have been trying to understand it....so far I have gotten it to work if there aren't any "x" 's, but it still runs after that, and then I still get the same error...it's a runtime error 91...ovject variable or with block variable not set.... I would post the whole code, but it is 5 printed out pages long....I will attach where I want it to stop.. Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(13, 1), Array(26, 1), Array(38, 1), _ Array(60, 1), Array(70, 1), Array(78, 1), Array(83, 1), Array(99, 1), Array(111, 1), _ Array(117, 1), Array(123, 1), Array(131, 1), Array(142, 1), Array(174, 1), _ Array(187, 1), Array(202, 1), Array(224, 1)) Cells.Select ActiveWindow.Zoom = 80 Cells.EntireColumn.AutoFit Sheets().Copy After:=Sheets(1) Sheets(2).Name = "Modified" On Error GoTo EndSub Columns("C:C").Find(What:="x").Select EndSub: MsgBox """Projects""Not Found" And there is more after that "Jim Rech" wrote: I don't have your whole sub so I can't rewrite it. However, I think you'd learn more and be much better off if you tried to understand what I gave you and applied it to your sub. That was really my point - showing how to handle the error raised by Find failing. -- Jim "Frantic Excel-er" wrote in message ... | Jim... | | Thanks for the reply... | | However, I cannot figure out how to incorporate this macro into the existing | macro that I already have.... | | I tried to just take out the "sub" and "end sub" parts, but then it doesn't | work right. Also, I only need a msg box if it finds no "x", not if it does. | | Any more ideas? | | "Jim Rech" wrote: | | This illustrates one way to handle the error: | | Sub a() | On Error GoTo EndThis | Columns("C:C").Find(What:="x").EntireRow.Insert | MsgBox """x"" found" | Exit Sub | EndThis: | MsgBox """x"" not found" | End Sub | | | -- | Jim | "Frantic Excel-er" wrote in | message ... | | Hi All, | | | | I currently have a huge macro that I use to import .asc files into excel | for | | validation, and other things. This macro separates project accounts from | non | | project accounts and performs a series of tasks on the project accounts. | The | | project accounts can be differentiated from non project accounts because | they | | all have an "X" in them... | | | | So, in my code, I first have to import the delimited data, set up column | | breaks, and then sort by my account column to get all of the account data | at | | the bottom of the wkst...(code is already written for this, and I have | been | | working on it for a month, so I don't want to change it). With that said, | I | | have my macro find "X" and then put a page break between all the project | | accounts, and the non project accounts so I can then do all my tasks with | | project accounts... | | | | So, now that I explained some stuff....here is the problem (question)... | | If the macro does not find any "x" in column C, then it will pop up an | error | | saying macro can't run...etc...etc.... | | | | What I would like for it to do is pop up a message box that says " No | | Project Accounts Found", and then have the macro stop there, because all | | other tasks past that point are for the projects. | | | | Here is what part of my code looks like: | | | | (lots of stuff before this...vlookup tables, formatting,etc) | | | | Sheets("Modified").Select | | Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, | _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | Columns("C:C").Find(What:="x").EntireRow.Insert | | Selection.End(xlDown).Select | | Selection.End(xlDown).Select | | Range(Selection, Selection.End(xlDown)).Select | | Range(Selection, Selection.End(xlToRight)).Select | | Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, | Header:=xlGuess, _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | | | Any suggestions on how I can get it to put that message box in?????? | | | | Thanks in advance, and sorry for being wordy.... | | | | Sara | | | |
#6
|
|||
|
|||
Frantic,
This part seems correct, but I prefer to use a name that doesn't appear to be code when examined: On Error GoTo NotFound: Columns("C:C").Find(What:="x").Select But at the very bottom of your sub, just inside the End Sub line, put these lines: Exit Sub NotFound: MsgBox """Projects""Not Found" And then your existing End Sub That way, when x is not found, the error is raised, you get your msgbox, and all is well. If it is found, processing continues, down until the Exit Sub just above the NotFound: line. HTH, Bernie MS Excel MVP "Frantic Excel-er" wrote in message ... Jim... I have been trying to understand it....so far I have gotten it to work if there aren't any "x" 's, but it still runs after that, and then I still get the same error...it's a runtime error 91...ovject variable or with block variable not set.... I would post the whole code, but it is 5 printed out pages long....I will attach where I want it to stop.. Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(13, 1), Array(26, 1), Array(38, 1), _ Array(60, 1), Array(70, 1), Array(78, 1), Array(83, 1), Array(99, 1), Array(111, 1), _ Array(117, 1), Array(123, 1), Array(131, 1), Array(142, 1), Array(174, 1), _ Array(187, 1), Array(202, 1), Array(224, 1)) Cells.Select ActiveWindow.Zoom = 80 Cells.EntireColumn.AutoFit Sheets().Copy After:=Sheets(1) Sheets(2).Name = "Modified" On Error GoTo EndSub Columns("C:C").Find(What:="x").Select EndSub: MsgBox """Projects""Not Found" And there is more after that "Jim Rech" wrote: I don't have your whole sub so I can't rewrite it. However, I think you'd learn more and be much better off if you tried to understand what I gave you and applied it to your sub. That was really my point - showing how to handle the error raised by Find failing. -- Jim "Frantic Excel-er" wrote in message ... | Jim... | | Thanks for the reply... | | However, I cannot figure out how to incorporate this macro into the existing | macro that I already have.... | | I tried to just take out the "sub" and "end sub" parts, but then it doesn't | work right. Also, I only need a msg box if it finds no "x", not if it does. | | Any more ideas? | | "Jim Rech" wrote: | | This illustrates one way to handle the error: | | Sub a() | On Error GoTo EndThis | Columns("C:C").Find(What:="x").EntireRow.Insert | MsgBox """x"" found" | Exit Sub | EndThis: | MsgBox """x"" not found" | End Sub | | | -- | Jim | "Frantic Excel-er" wrote in | message ... | | Hi All, | | | | I currently have a huge macro that I use to import .asc files into excel | for | | validation, and other things. This macro separates project accounts from | non | | project accounts and performs a series of tasks on the project accounts. | The | | project accounts can be differentiated from non project accounts because | they | | all have an "X" in them... | | | | So, in my code, I first have to import the delimited data, set up column | | breaks, and then sort by my account column to get all of the account data | at | | the bottom of the wkst...(code is already written for this, and I have | been | | working on it for a month, so I don't want to change it). With that said, | I | | have my macro find "X" and then put a page break between all the project | | accounts, and the non project accounts so I can then do all my tasks with | | project accounts... | | | | So, now that I explained some stuff....here is the problem (question)... | | If the macro does not find any "x" in column C, then it will pop up an | error | | saying macro can't run...etc...etc.... | | | | What I would like for it to do is pop up a message box that says " No | | Project Accounts Found", and then have the macro stop there, because all | | other tasks past that point are for the projects. | | | | Here is what part of my code looks like: | | | | (lots of stuff before this...vlookup tables, formatting,etc) | | | | Sheets("Modified").Select | | Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, | _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | Columns("C:C").Find(What:="x").EntireRow.Insert | | Selection.End(xlDown).Select | | Selection.End(xlDown).Select | | Range(Selection, Selection.End(xlDown)).Select | | Range(Selection, Selection.End(xlToRight)).Select | | Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, | Header:=xlGuess, _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | | | Any suggestions on how I can get it to put that message box in?????? | | | | Thanks in advance, and sorry for being wordy.... | | | | Sara | | | |
#7
|
|||
|
|||
Bernie......it appears to be working GREAT....thank you so much......I
couldn't figure out to put the end this at the end of all the code.... Thanks again.......now all bugs, and other issues have been worked out of my giant macro to import .asc files into excel..... YAAAAYYYYYYY....that is a real treat right before a holiday weekend!!!!! "Bernie Deitrick" wrote: Frantic, This part seems correct, but I prefer to use a name that doesn't appear to be code when examined: On Error GoTo NotFound: Columns("C:C").Find(What:="x").Select But at the very bottom of your sub, just inside the End Sub line, put these lines: Exit Sub NotFound: MsgBox """Projects""Not Found" And then your existing End Sub That way, when x is not found, the error is raised, you get your msgbox, and all is well. If it is found, processing continues, down until the Exit Sub just above the NotFound: line. HTH, Bernie MS Excel MVP "Frantic Excel-er" wrote in message ... Jim... I have been trying to understand it....so far I have gotten it to work if there aren't any "x" 's, but it still runs after that, and then I still get the same error...it's a runtime error 91...ovject variable or with block variable not set.... I would post the whole code, but it is 5 printed out pages long....I will attach where I want it to stop.. Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(13, 1), Array(26, 1), Array(38, 1), _ Array(60, 1), Array(70, 1), Array(78, 1), Array(83, 1), Array(99, 1), Array(111, 1), _ Array(117, 1), Array(123, 1), Array(131, 1), Array(142, 1), Array(174, 1), _ Array(187, 1), Array(202, 1), Array(224, 1)) Cells.Select ActiveWindow.Zoom = 80 Cells.EntireColumn.AutoFit Sheets().Copy After:=Sheets(1) Sheets(2).Name = "Modified" On Error GoTo EndSub Columns("C:C").Find(What:="x").Select EndSub: MsgBox """Projects""Not Found" And there is more after that "Jim Rech" wrote: I don't have your whole sub so I can't rewrite it. However, I think you'd learn more and be much better off if you tried to understand what I gave you and applied it to your sub. That was really my point - showing how to handle the error raised by Find failing. -- Jim "Frantic Excel-er" wrote in message ... | Jim... | | Thanks for the reply... | | However, I cannot figure out how to incorporate this macro into the existing | macro that I already have.... | | I tried to just take out the "sub" and "end sub" parts, but then it doesn't | work right. Also, I only need a msg box if it finds no "x", not if it does. | | Any more ideas? | | "Jim Rech" wrote: | | This illustrates one way to handle the error: | | Sub a() | On Error GoTo EndThis | Columns("C:C").Find(What:="x").EntireRow.Insert | MsgBox """x"" found" | Exit Sub | EndThis: | MsgBox """x"" not found" | End Sub | | | -- | Jim | "Frantic Excel-er" wrote in | message ... | | Hi All, | | | | I currently have a huge macro that I use to import .asc files into excel | for | | validation, and other things. This macro separates project accounts from | non | | project accounts and performs a series of tasks on the project accounts. | The | | project accounts can be differentiated from non project accounts because | they | | all have an "X" in them... | | | | So, in my code, I first have to import the delimited data, set up column | | breaks, and then sort by my account column to get all of the account data | at | | the bottom of the wkst...(code is already written for this, and I have | been | | working on it for a month, so I don't want to change it). With that said, | I | | have my macro find "X" and then put a page break between all the project | | accounts, and the non project accounts so I can then do all my tasks with | | project accounts... | | | | So, now that I explained some stuff....here is the problem (question)... | | If the macro does not find any "x" in column C, then it will pop up an | error | | saying macro can't run...etc...etc.... | | | | What I would like for it to do is pop up a message box that says " No | | Project Accounts Found", and then have the macro stop there, because all | | other tasks past that point are for the projects. | | | | Here is what part of my code looks like: | | | | (lots of stuff before this...vlookup tables, formatting,etc) | | | | Sheets("Modified").Select | | Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, | _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | Columns("C:C").Find(What:="x").EntireRow.Insert | | Selection.End(xlDown).Select | | Selection.End(xlDown).Select | | Range(Selection, Selection.End(xlDown)).Select | | Range(Selection, Selection.End(xlToRight)).Select | | Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, | Header:=xlGuess, _ | | OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom | | | | Any suggestions on how I can get it to put that message box in?????? | | | | Thanks in advance, and sorry for being wordy.... | | | | Sara | | | |
#8
|
|||
|
|||
YAAAAYYYYYYY....that is a real treat right before a holiday weekend!!!!!
So I guess this means that we all get the weekend off! <vbg Bernie |
#9
|
|||
|
|||
LOL.......hehehehehehe
Guess that does mean we all get the weekend off.... I thought these forums were only available M-F 8:00-5:00... LOL "Bernie Deitrick" wrote: YAAAAYYYYYYY....that is a real treat right before a holiday weekend!!!!! So I guess this means that we all get the weekend off! <vbg Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use find in a macro in Excel | Excel Worksheet Functions | |||
How can avoid the security Macro message ... | Excel Discussion (Misc queries) | |||
Cannot find macro | New Users to Excel | |||
UDF and Calculation tree | Links and Linking in Excel | |||
Can't find macro | Excel Worksheet Functions |