assign number value
 

Hi Tim:
This worksheet_change event procedure works for column K.
Adjust the isect range to whatever column(s) you need..

Private Sub Worksheet_Change(ByVal target As Range)
Dim isect As Range, c As Range
On Error GoTo Cleanup

Set isect = intersect(target, Range("K:K"))
If Not (isect Is Nothing) Then
Application.EnableEvents = False
For Each c In isect
If c.Value < "" Then
c.Value = 0
End If
Next c
End If

Cleanup:
Application.EnableEvents = True
End Sub

hth,
Jim Feaver

"Tim" wrote in message
...
I would like to make a column reject anything other than a number for the
column. If a word is put in there by a macro excel sees it is not a number
and replaces it with a 0 or blank.

assign number value
 

Tim:
D'oh! I somehow managed to post the wrong copy
of the procedure. Here is the correct one,
along with a variation.

The use of isect enables it to assign a zero
no matter if one cell in the target row is
edited, copied/cut and pasted or if a range
of cells that includes or spans the target row
is copied/cut and pasted.
It also allows you to delete a zero or zeroes
in the target column either individually
or by selected any range that includes
or spans the target row.

If you prefer non-numeric values to become
blanks instead of zeroes, then use the second
procedure, which is just a slight variation of the
first one.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range, c As Range
On Error GoTo Cleanup

Set isect = Intersect(Target, Range("K:K"))
If Not (isect Is Nothing) Then
Application.EnableEvents = False
For Each c In isect

If c.Value < "" And _
Not (IsNumeric(c.Value)) Then
c.Value = 0
End If
Next c
End If

Cleanup:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range, c As Range
On Error GoTo Cleanup

Set isect = Intersect(Target, Range("K:K"))
If Not (isect Is Nothing) Then
Application.EnableEvents = False
For Each c In isect
If Not (IsNumeric(c.Value)) Then
c.Value = ""
End If
Next c
End If

Cleanup:
Application.EnableEvents = True
End Sub


hth,
Jim Feaver

The
"Jim Feaver" wrote in message
news:1ixUb.408600$ts4.158286@pd7tw3no...
Hi Tim:
This worksheet_change event procedure works for column K.
Adjust the isect range to whatever column(s) you need..

Private Sub Worksheet_Change(ByVal target As Range)
Dim isect As Range, c As Range
On Error GoTo Cleanup

Set isect = intersect(target, Range("K:K"))
If Not (isect Is Nothing) Then
Application.EnableEvents = False
For Each c In isect
If c.Value < "" Then
c.Value = 0
End If
Next c
End If

Cleanup:
Application.EnableEvents = True
End Sub

hth,
Jim Feaver

"Tim" wrote in message
...
I would like to make a column reject anything other than a number for
the
column. If a word is put in there by a macro excel sees it is not a number
and replaces it with a 0 or blank.