Filling Blank Cells

[Learning Excel]

Hi,
I have a workbook with a number of sheets.
I am trying to fill up some cells that are blank and have this crude code.
However, the macro will only work if I go to each sheet and run the macro
which this is not what I want.

I have save this macro in a module and would hope that it can run fill
up all the sheets that have blank cells as in the ranges when the macro is run

Hope some guidance on this.


Sub FillBlank()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
If Range("B36") = "" Then Range("B36") = 0
If Range("C36") = "" Then Range("C36") = 0
If Range("D36") = "" Then Range("D36") = 0

If Range("B43") = "" Then Range("B43") = 0
If Range("C43") = "" Then Range("C43") = 0
If Range("D43") = "" Then Range("D43") = 0

If Range("B50") = "" Then Range("B50") = 0
If Range("C50") = "" Then Range("C50") = 0
If Range("D50") = "" Then Range("D50") = 0

If Range("B57") = "" Then Range("B57") = 0
If Range("C57") = "" Then Range("C57") = 0
If Range("D57") = "" Then Range("D57") = 0

Next ws

End Sub

Thank
Note : I only want to fill the above ranges which is blank and not any other rows although they may be blank

NB: I have to fill the cells because I have many workbooks which I need to
extra data and this blank cells are giving me problem row offset to paste
the data and would fill up incorrectly due to the blank cell

 

[amigos]

try:

Sub FillGaps()

Dim ws As Worksheet

Dim i As Byte
Dim j As Byte

For Each ws In ActiveWorkbook.Worksheets

For j = 36 To 57 Step 7

For i = 2 To 4
If ws.Cells(j, i) = "" Then ws.Cells(j, i) = 0
Next i

Next j

Next ws

End Sub


HTH

 

[Learning Excel]

Hi,
It is working as intended. Thank you for your time and help.

I do not know why my code (Filling blank) does not cycle through each
sheet to fill the blank and your is working although I have changed mine
from For Each ws In Worksheets to For Each ws In ActiveWorkbook.Worksheets

Should not the code For Each ws in ... Next ws cycle through all the sheets?

Appreciate some enlightenment.
Learning Excel

 

[erik.van.geit]

Just changing your code to
If ws.Range("B36") = "" Then ws.Range("B36") = 0
would already help

You were doing it "for" each worksheet - counting some loops - but not "on" each worksheet.

you can see that in the code amigos provided:
If ws.Cells(j, i) = "" Then ws.Cells(j, i) = 0

kind regards,
Erik

 

[Learning Excel]

Hi,
Thank you for the enlightenment. Macro is so "abstract"... for me.

Learning Excel

 

[erik.van.geit]

You're welcome,

To make it less abstract you can step through it to SEE what happens.
Click in your code, put the "codescreen" (Visual Basic Editor) aside and hit F8, look at your screen what's happening.
To run to a certain point click in that line and hit F9, then F5 to run...

You can find all this in the menu "Debug" (I hope that's the right English terminology)

kind regards,
Erik