Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with Excel

Posted on 2012-08-24
5
Medium Priority
?
538 Views
Last Modified: 2012-08-24
Every morning I am getting several text files from IT. Then I put them into Excel sheets  through data Import. Now I have let say four sheets with four tables. Here is the example of one table  
Rec#      Field1      Field2      Field3
1      23      yy      ee
2             ee      rr
3      23      tt      rr
4      21      ee      ee
5      33      gg      hh


As you can see Fields1 has empty (null) value

So I want the help how update all blanks with “0”  at once
 in all four sheets
0
Comment
Question by:rfedorov
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 38330507
If your import starts in cell a1, you can try

sub replacestuff
dim sht as worksheet

for each sht in activeworkbook.sheets
sht.cells(1,1).currentregion.specialcells(xlcelltypeblanks).value=0
next

end sub

Open in new window


Thomas
0
 

Author Comment

by:rfedorov
ID: 38331058
great that is what i need
well deserved points
and the very last thing ( i can open a new question)
how to put total number of records after the fixing blanks

Rec#      Field1      Field2      Field3
1           23               yy               ee
2           0                  ee                rr
3          23                 tt                  rr
4             21               ee                ee
5              33               gg                hh

Total         5
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38331151
Try this update

sub replacestuff
dim sht as worksheet


for each sht in activeworkbook.sheets
sht.cells(1,1).currentregion.specialcells(xlcelltypeblanks).value=0

sht.cells(sht.rows.count,1).end(xlup).offset(2)="Total"
sht.cells(sht.rows.count,1).end(xlup).offset(2,1).formular1c1="=Count(R2C:R[-2]C)"

next

end sub

Open in new window

0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38331154
if some of your sheets do not have blank cells, you'll get an error with that macro.

You could avoid it by putting on error resume next after the dim line
0
 

Author Closing Comment

by:rfedorov
ID: 38331310
THANK YOU VERY MUCH
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question