If cell blank = $0.00?

[EdaddyJ]

Can you tell me whats wrong here? I know it has something to do with the range. Or give me a better solution?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Sheets(1).Range("=D23:E304")
For Each cell In rng
If cell.Value = 0 Or cell.Value = Null Or cell.Value = " " Then
cell.Value = "$0.00"
End If
End Sub

 

[Hermanito]

Why do you use the SelectionChange event? Just curious...
Must this really be evaluated everytime you change selection?
Seems like overkill to me... why not use the Change event, won't
fire as often, so that this sub will not slow things down too much?

As for the Range: change
("=D23:E304") to ("D23:E304")
the Sheets(1). is also unnecessary I think... try to remove that as well.

 

[EdaddyJ]

I don't know why I do things I just do them =-)
Someone gave me this solution before so I copied it from my other sheet and tried to duplicate the same results. Problem is I am trying to get the results in cells that contain formulas so #DIV/0! is still me result. Do you have any suggestions?

 

[EdaddyJ]

it is telling me variable not defined and highlighting CELL

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("D23:E304")
For Each cell In rng
If cell.Value = 0 Or cell.Value = Null Or cell.Value = " " Then
cell.Value = "$0.00"
End If
End Sub

 

[Hermanito]

Could you post a sample of what data you have, and what it should become after whatever macro had its way with it?
Do you actually want to remove the formulas that give error-results and put $0.00 in their place? Or only put $0.00 in really empty cells and hide results from formulas only IF they give an error?

There is some more input from you required before we have an idea of what you want to achieve...

 

[Hermanito]

add the line
Dim cell as Range
right below the other Dim line might help

 

[EdaddyJ]

A B C D
1 Name Job% Labor Labor Burden
2 Erin 100% 1000 350
3 Job 1 50% 500 175
4 Job 2 25% 250 87.5
5 Job 3 25% 250 87.5

I enter in the Labor and Labor Burden totals in cells C2 and D2 and in all of column C and it calculates the percentage then the Labor Burden dollar. But if C2 is blank it gives me an error. I need it to show $0.00.

I hope I explained that well.

 

[Hermanito]

I think you wouldn't need VBA to do that...
You can use worksheet functions like IF to do that...
What is the formula you are using that gives the error?

 

[EdaddyJ]

That's a good solution but I was trying to do it all at once. Other wise I have 300 if statements to write in 300 different cells. Which I could do in about the same amount of time as figure this out but I was hoping to figure out a better way for later sheets.

 

[Hermanito]

you'd have to write the if-statement once, then doubleclick the autofill handle and you'd be done... doesn't get much easier I guess...