Change all numbers to zero

F

FrankM

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

Gary''s Student

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

Simon Lloyd

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

Code
-------------------
Sub AllZero(
Dim Sh As Workshee
Dim MyCell As Rang
For Each Sh In Sheet
For Each MyCell In Sh.UsedRang
If IsNumeric(MyCell) The
MyCell.Value =
End I
Next MyCel
Next S
End Su
-------------------

FrankM;185785 said:
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 number
o
all the spreadsheets to zero

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

Kindda a strange request, I know
Click to expand...

--
Simon Lloy

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

Mike H

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(xlCellTypeConstants,
1)
c.Value = 0
Next
Next
Next wbk
End Sub

Mike
 
J

JLGWhiz

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

JLGWhiz

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

Gord Dibben

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
 
K

keiji kounoike

Try this one

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

keiji
 
K

keiji kounoike

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
 
S

Simon Lloyd

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




Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
(http://www.thecodecage.com/forumz/showthread.php?t=51306
Click to expand...
Simo

Open a new blank workbook

On sheet1 enter 1 to 5 in A1:A

Enter "I'll be darned" in H2

Run your macro

What are results


Gord Dibben MS Excel MV


On Sat, 17 Jan 2009 00:13:36 +0000, Simon Lloy
Click to expand...

--
Simon Lloy

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