1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro - enter in data in cell if Null/Empty

Discussion in 'Business Applications' started by chudok01, Nov 29, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    201
    I have an issue if certain cells are blank (not sure if they would be null, blank, empty) to have a macro to input "0" in the cell instead. When I look at the format in excel it's under general.

    I currently have the following and it puts nothing in the cell. (or seems to be nothing in the cell) I can manually enter a "0"(zero) in the cell and it shows.....

    Sub Addifblank49()
    Sheets("BPS").Select
    Range("c16").Select
    Var = Selection.Value
    If IsNull(Range("C16").Select) Then Var = "0"
    End Sub


    I've tried using val instead of var and it won't let me. I'm not real familiar with VBA so any help would be awesome!!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Try it this wy:

    Code:
    Sub Addifblank49()
    Sheets("BPS").Select
    Range("c16").Select
    if activecell.value = vbnullstring then activecell.value = 0
    End Sub
    
    or this one
    
    Sub Addifblank49()
    if Sheets("BPS").Range("c16").value = vbnullstring then _
      Sheets("BPS").Range("c16").value = 0
    End Sub
    
    
    All this does is check if range("C16") is blank, empty (that is vbnullstring) then put a 0 in it

    I'm sure you have more than one cell so then you'll have to a loop somewhere that loops though all the cells you vwant a 0 in
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    Why not select the entire range, press F5, select Special, select Blanks, hit 0, then Ctrl + Enter?
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Sure but if he needs lines of code to select particular cells in a manner of code?
    Zack, he could also record a macro with your steps and maybe it'll work for different ocasions?
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Sure, or a one-liner...

    Code:
    On Error Resume next
    Range("specify range here").SpecialCells(xlcelltypeblanks).value = 0
    The only limitation of the above code would be the last used cell, if the specified range went past that it wouldn't be affected.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    you'd have to code it to allow for the last filled cell (?)
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Not really. It just won't apply the value to anything past the last used cell, even if the specified range goes beyond it. You can replicate it by creating a new worksheet, enter any data in cell D5, then set the range of the above code to A1:E6. The entire range won't fill, only A1:D5 (excluding any cells with data in it). So the limit (of the bottom-right) is the last used cell, as recognized by Excel. As far as I am aware this is a limitation of the SpecialCells method.
     
  8. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    201
    :eek: She does need it for a particular cell. The cell never changes when data is brought in from an outside source. So it is much easier to just point macro to a certain cell because I do have other cells that are blank and need to stay blank. Thank you Keelellah it worked perfectly. (y)
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Perfect. Glad to have been of assitance. :)
    Zack, I'll take a look at the last filled cell, I have something there, for other necessities. (y)

    PS Don't forget to mark it solved
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/965490

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice