asked on

Help with Excel

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

nutsch

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

Roman F

ASKER

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

nutsch

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

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman F

ASKER

THANK YOU VERY MUCH