Page 1 of 2 12 LastLast
Results 1 to 10 of 11

If cell blank = $0.00?

This is a discussion on If cell blank = $0.00? within the Excel Questions forums, part of the Question Forums category; Can you tell me whats wrong here? I know it has something to do with the range. Or give me ...

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Posts
    115

    Default If cell blank = $0.00?

    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

  2. #2
    Board Regular Hermanito's Avatar
    Join Date
    Apr 2007
    Posts
    1,238

    Default

    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.
    It is better to remain silent and be thought a fool, than to speak and remove all doubt. (Abraham Lincoln)
    Real programmers don't comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
    My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don't want to have a seance with my ancestors to know what's not working for you!

  3. #3
    Board Regular
    Join Date
    Feb 2007
    Posts
    115

    Default

    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?

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Posts
    115

    Default

    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

  5. #5
    Board Regular Hermanito's Avatar
    Join Date
    Apr 2007
    Posts
    1,238

    Default

    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...
    It is better to remain silent and be thought a fool, than to speak and remove all doubt. (Abraham Lincoln)
    Real programmers don't comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
    My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don't want to have a seance with my ancestors to know what's not working for you!

  6. #6
    Board Regular Hermanito's Avatar
    Join Date
    Apr 2007
    Posts
    1,238

    Default

    add the line
    Dim cell as Range
    right below the other Dim line might help
    It is better to remain silent and be thought a fool, than to speak and remove all doubt. (Abraham Lincoln)
    Real programmers don't comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
    My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don't want to have a seance with my ancestors to know what's not working for you!

  7. #7
    Board Regular
    Join Date
    Feb 2007
    Posts
    115

    Default

    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.

  8. #8
    Board Regular Hermanito's Avatar
    Join Date
    Apr 2007
    Posts
    1,238

    Default

    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?
    It is better to remain silent and be thought a fool, than to speak and remove all doubt. (Abraham Lincoln)
    Real programmers don't comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
    My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don't want to have a seance with my ancestors to know what's not working for you!

  9. #9
    Board Regular
    Join Date
    Feb 2007
    Posts
    115

    Default

    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.

  10. #10
    Board Regular Hermanito's Avatar
    Join Date
    Apr 2007
    Posts
    1,238

    Default

    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...
    It is better to remain silent and be thought a fool, than to speak and remove all doubt. (Abraham Lincoln)
    Real programmers don't comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
    My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don't want to have a seance with my ancestors to know what's not working for you!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com