Change all numbers to zero
 

OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.

Change all numbers to zero
 

Sub zeroo()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
If IsNumeric(r.Value) Then
r.Value = 0
End If
Next
End Sub

--
Gary''s Student - gsnu200827


"FrankM" wrote:

OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.

Change all numbers to zero
 

You're right and odd request but put this code in a standard module and
run it, all cells containing just numbers will become zero anything else
will be left!

Code:
--------------------
Sub AllZero()
Dim Sh As Worksheet
Dim MyCell As Range
For Each Sh In Sheets
For Each MyCell In Sh.UsedRange
If IsNumeric(MyCell) Then
MyCell.Value = 0
End If
Next MyCell
Next Sh
End Sub
--------------------


FrankM;185785 Wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers
on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers
on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51306

Change all numbers to zero
 

Hi,

Every sheet in every open workbook!! Hmm drastic indeed

This goes in a general module

Sub Drastic_Action()
Dim wbk As Workbook
Dim ws As Worksheet
For Each wbk In Workbooks
For x = 1 To wbk.Worksheets.Count
On Error Resume Next
For Each c In Sheets(x).UsedRange.SpecialCells(xlCellTypeConstan ts,
1)
c.Value = 0
Next
Next
Next wbk
End Sub

Mike

"FrankM" wrote:

OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.

Change all numbers to zero
 

I believe the OP only wanted cells that had values to be changed to zero.
Without some qualifications, IsNumeric() will read an empty cell as Numeric
and change it to zero even though it had no value in it.

"Simon Lloyd" wrote:


You're right and odd request but put this code in a standard module and
run it, all cells containing just numbers will become zero anything else
will be left!

Code:
--------------------
Sub AllZero()
Dim Sh As Worksheet
Dim MyCell As Range
For Each Sh In Sheets
For Each MyCell In Sh.UsedRange
If IsNumeric(MyCell) Then
MyCell.Value = 0
End If
Next MyCell
Next Sh
End Sub
--------------------


FrankM;185785 Wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers
on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers
on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51306

Change all numbers to zero
 

Agreed but only to a point!, when you say "empty" you meant previously
used as unused cells are not affectedJLGWhiz;185968 Wrote:
I believe the OP only wanted cells that had values to be changed to
zero.
Without some qualifications, IsNumeric() will read an empty cell as
Numeric
and change it to zero even though it had no value in it.

"Simon Lloyd" wrote:


You're right and odd request but put this code in a standard module
and
run it, all cells containing just numbers will become zero anything
else
will be left!

Code:
--------------------
Sub AllZero()
Dim Sh As Worksheet
Dim MyCell As Range
For Each Sh In Sheets
For Each MyCell In Sh.UsedRange
If IsNumeric(MyCell) Then
MyCell.Value = 0
End If
Next MyCell
Next Sh
End Sub
--------------------


FrankM;185785 Wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the
numbers
on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the
numbers
on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Change all numbers to zero - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=51306)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51306

Change all numbers to zero
 

empty by any other name is empty. having nothing in it, including formuals
that return a zero lenth string ("") that would appear to be empty.

"Simon Lloyd" wrote:


Agreed but only to a point!, when you say "empty" you meant previously
used as unused cells are not affectedJLGWhiz;185968 Wrote:
I believe the OP only wanted cells that had values to be changed to
zero.
Without some qualifications, IsNumeric() will read an empty cell as
Numeric
and change it to zero even though it had no value in it.

"Simon Lloyd" wrote:


You're right and odd request but put this code in a standard module
and
run it, all cells containing just numbers will become zero anything
else
will be left!

Code:
--------------------
Sub AllZero()
Dim Sh As Worksheet
Dim MyCell As Range
For Each Sh In Sheets
For Each MyCell In Sh.UsedRange
If IsNumeric(MyCell) Then
MyCell.Value = 0
End If
Next MyCell
Next Sh
End Sub
--------------------


FrankM;185785 Wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the
numbers
on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the
numbers
on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Change all numbers to zero - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=51306)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51306

Change all numbers to zero
 

Simon

Open a new blank workbook.

On sheet1 enter 1 to 5 in A1:A5

Enter "I'll be darned" in H20

Run your macro.

What are results?


Gord Dibben MS Excel MVP


On Sat, 17 Jan 2009 00:13:36 +0000, Simon Lloyd
wrote:

Agreed but only to a point!, when you say "empty" you meant previously
used as unused cells are not affected

Change all numbers to zero
 

Try this one

Sub allnum2zero()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) = 0
End Sub

keiji

FrankM wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.

Change all numbers to zero
 

I forgot about all th spreadsheets part. Try this modified one.

Sub allnum2zero()
Dim sh As Worksheet
On Error Resume Next
For Each sh In Worksheets
sh.Cells.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
sh.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) = 0
Next
End Sub

keiji

keiji kounoike wrote:
Try this one

Sub allnum2zero()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) = 0
End Sub

keiji

FrankM wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the
numbers on all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the
numbers on all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.

Change all numbers to zero
 

Gents, I truly do understand where you are coming from, however, lets
say you have data on sheet 1 A1:H10 consisting of numbers, letters and a
mixture, now cpy that range and pste it to lets say F6 sheet 2 and
perhaps K12 sheet 3, run my code, you will see that only those area's
will be changed, granted whenever you use a cell outside of that (hence
my first reply) the usedrange changes!, anyway as a quick fix to the
problem:
Code:
--------------------
Sub AllZero()
Dim Sh As Worksheet
Dim MyCell As Range
For Each Sh In Sheets
For Each MyCell In Sh.UsedRange
If MyCell = vbnullstring then
ElseIf IsNumeric(MyCell) Then
MyCell.Value = 0
End If
Next MyCell
Next Sh
End Sub
--------------------
JLGWhiz;186204 Wrote:
empty by any other name is empty. having nothing in it, including
formuals
that return a zero lenth string ("") that would appear to be empty.

"Simon Lloyd" wrote:


Agreed but only to a point!, when you say "empty" you meant
previously
used as unused cells are not affectedJLGWhiz;185968 Wrote:
I believe the OP only wanted cells that had values to be changed to
zero.
Without some qualifications, IsNumeric() will read an empty cell as
Numeric
and change it to zero even though it had no value in it.

"Simon Lloyd" wrote:


You're right and odd request but put this code in a standard
module
and
run it, all cells containing just numbers will become zero
anything
else
will be left!

Code:
--------------------
Sub AllZero()
Dim Sh As Worksheet
Dim MyCell As Range
For Each Sh In Sheets
For Each MyCell In Sh.UsedRange
If IsNumeric(MyCell) Then
MyCell.Value = 0
End If
Next MyCell
Next Sh
End Sub
--------------------


FrankM;185785 Wrote:
OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the
numbers
on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the
numbers
on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' ('The Code Cage'
(http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile:
Simon
Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd'
(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: 'Change all numbers to zero - The Code Cage
Forums'
('Change all numbers to zero - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=51306))




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Change all numbers to zero - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=51306)



Gord Dibben;186218 Wrote:
Simon

Open a new blank workbook.

On sheet1 enter 1 to 5 in A1:A5

Enter "I'll be darned" in H20

Run your macro.

What are results?


Gord Dibben MS Excel MVP


On Sat, 17 Jan 2009 00:13:36 +0000, Simon Lloyd
wrote:

Agreed but only to a point!, when you say "empty" you meant previously
used as unused cells are not affected


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51306