Can you have a macro always running on a sheet waiting for data
 

The subject line, pretty much sums it up. This is the macro I have, as I
input data I want it to change without executing the macro, (i.e, the purpose
of the question.)

Range("A1:A65536").Select
Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Can you have a macro always running on a sheet waiting for data
 

You might be interested in using event procedures, especially the
Worksheet_Change event. See
http://www.cpearson.com/excel/events.htm for more information
about events.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Sean" wrote in message
...
The subject line, pretty much sums it up. This is the macro I
have, as I
input data I want it to change without executing the macro,
(i.e, the purpose
of the question.)

Range("A1:A65536").Select
Selection.Replace What:="39", Replacement:="0",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Can you have a macro always running on a sheet waiting for dat
 

Thanks Chip, I'll take a look however my idea of using this macro for my
first intention has backfired.

I had the macro originally planned to do the reverse, all I want is a simple
find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then
change it to a 0. Again this didnt work the way I thought it would I thought
I could replace the ="39" with an < = ({"99","66","33","11"}). Well as you
can guess it didnt work. Any ideas.



"Chip Pearson" wrote:

You might be interested in using event procedures, especially the
Worksheet_Change event. See
http://www.cpearson.com/excel/events.htm for more information
about events.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Sean" wrote in message
...
The subject line, pretty much sums it up. This is the macro I
have, as I
input data I want it to change without executing the macro,
(i.e, the purpose
of the question.)

Range("A1:A65536").Select
Selection.Replace What:="39", Replacement:="0",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Can you have a macro always running on a sheet waiting for dat
 

Sean,

Paste this code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then
On Error GoTo err_handler
Application.EnableEvents = False
Select Case Target.Value
Case 99, 66, 33, 11
'do nothing
Case Else
Target.Value = 0
End Select
End If

err_handler:
Application.EnableEvents = True

End Sub

I have a question regarding the above. Is there a simple coding above to
have the four numbers be the first Case, so that I don't have the "do
nothing" bit as the first case?

hth,

Doug

"Sean" wrote in message
...
Thanks Chip, I'll take a look however my idea of using this macro for my
first intention has backfired.

I had the macro originally planned to do the reverse, all I want is a
simple
find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then
change it to a 0. Again this didnt work the way I thought it would I
thought
I could replace the ="39" with an < = ({"99","66","33","11"}). Well as
you
can guess it didnt work. Any ideas.



"Chip Pearson" wrote:

You might be interested in using event procedures, especially the
Worksheet_Change event. See
http://www.cpearson.com/excel/events.htm for more information
about events.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Sean" wrote in message
...
The subject line, pretty much sums it up. This is the macro I
have, as I
input data I want it to change without executing the macro,
(i.e, the purpose
of the question.)

Range("A1:A65536").Select
Selection.Replace What:="39", Replacement:="0",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Can you have a macro always running on a sheet waiting for dat
 

It keeps giving me an error on it, am I suppose to paste in with my current
module or just start from scratch? Either way it does not seem to be
working. Any help would be great. Thanks for the assistance so far....

Sean

"Doug Glancy" wrote:

Sean,

Paste this code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then
On Error GoTo err_handler
Application.EnableEvents = False
Select Case Target.Value
Case 99, 66, 33, 11
'do nothing
Case Else
Target.Value = 0
End Select
End If

err_handler:
Application.EnableEvents = True

End Sub

I have a question regarding the above. Is there a simple coding above to
have the four numbers be the first Case, so that I don't have the "do
nothing" bit as the first case?

hth,

Doug

"Sean" wrote in message
...
Thanks Chip, I'll take a look however my idea of using this macro for my
first intention has backfired.

I had the macro originally planned to do the reverse, all I want is a
simple
find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then
change it to a 0. Again this didnt work the way I thought it would I
thought
I could replace the ="39" with an < = ({"99","66","33","11"}). Well as
you
can guess it didnt work. Any ideas.



"Chip Pearson" wrote:

You might be interested in using event procedures, especially the
Worksheet_Change event. See
http://www.cpearson.com/excel/events.htm for more information
about events.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Sean" wrote in message
...
The subject line, pretty much sums it up. This is the macro I
have, as I
input data I want it to change without executing the macro,
(i.e, the purpose
of the question.)

Range("A1:A65536").Select
Selection.Replace What:="39", Replacement:="0",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Can you have a macro always running on a sheet waiting for dat
 

Sean,

Paste just the code I wrote into the code module for that sheet. You can
the module inside the VBE, or just right-click on the worksheet tab (in
Excel) and choose "View Code." Don't use your old code.

This code is a Change Event, like Chip referred to earlier. The event is
fired every time you change a value in the worksheet.

I realized that the code I gave before wouldn't work if you changed more
than one cell at a time. This does (I think). I've commented the code
below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed_column_A As Range
Dim changed_cell As Range

'If the change was range was at least part in Column A
If Not Intersect(Target, Columns(1)) Is Nothing Then
'If there's an error turn EnableEvents back on
On Error GoTo err_handler
'Define the part of the changed range in Column A
Set changed_column_A = Intersect(Target, Columns(1))
'Turn off Events so that this Change doesn't trigger another Change
event
Application.EnableEvents = False
'Go through the changed range one cell at a time
For Each changed_cell In changed_column_A
'Look at the possible changes to the changed range, called "Target"
Select Case changed_cell.Value
'If the value of Target now equals one of your specified values
Case 99, 66, 33, 11
'do nothing
'If the Target now equals any other value
Case Else
'change Target to 0
Target.Value = 0
End Select
Next changed_cell
End If
'in case of an error EnableEvents is turned back on.
'The code is also executed if there is no error
err_handler:
Application.EnableEvents = True

End Sub

hth,

Doug

"Sean" wrote in message
...
It keeps giving me an error on it, am I suppose to paste in with my
current
module or just start from scratch? Either way it does not seem to be
working. Any help would be great. Thanks for the assistance so far....

Sean

"Doug Glancy" wrote:

Sean,

Paste this code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then 'was the
change in that range?
On Error GoTo err_handler
Application.EnableEvents = False Select Case Target.Value
'Look at the possible changes to the changed range
Case 99, 66, 33, 11 'If the value of the changed range equalled
specified values
'do nothing
Case Else 'If the value equalled anything else
Target.Value = 0 'change it to 0
End Select
End If

err_handler:
Application.EnableEvents = True

End Sub

I have a question regarding the above. Is there a simple coding above to
have the four numbers be the first Case, so that I don't have the "do
nothing" bit as the first case?

hth,

Doug

"Sean" wrote in message
...
Thanks Chip, I'll take a look however my idea of using this macro for
my
first intention has backfired.

I had the macro originally planned to do the reverse, all I want is a
simple
find any cell in range A1:A65536 and if its not equel to 99,66,33,11
then
change it to a 0. Again this didnt work the way I thought it would I
thought
I could replace the ="39" with an < = ({"99","66","33","11"}). Well
as
you
can guess it didnt work. Any ideas.



"Chip Pearson" wrote:

You might be interested in using event procedures, especially the
Worksheet_Change event. See
http://www.cpearson.com/excel/events.htm for more information
about events.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Sean" wrote in message
...
The subject line, pretty much sums it up. This is the macro I
have, as I
input data I want it to change without executing the macro,
(i.e, the purpose
of the question.)

Range("A1:A65536").Select
Selection.Replace What:="39", Replacement:="0",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub