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

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

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

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
|
|
|

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
|
|
|

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
|
|
|

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
|
|
|

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

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