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 2007 Macro Question

Discussion in 'Business Applications' started by shooozy, Jan 28, 2011.

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

    shooozy Thread Starter

    Joined:
    Jan 28, 2011
    Messages:
    4
    Hi All,

    I'm trying to create a macro that will fill cells within a column with a zero when the cell is blank and leave the cell as-is when it is populated with a value. I figured out how to fill a column with zero down to the last active row in the sheet but I need to revise it to skip the cells with values.

    Please help, I'm stuck at this point and it is the last part of the macro that I cannot figure out.

    Thank you!

    Jen
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Don't need a macro for that. =If(Len(A1)>0,A1,0)
     
  3. shooozy

    shooozy Thread Starter

    Joined:
    Jan 28, 2011
    Messages:
    4
    Hi,

    Well I am trying to incorportate multiple formatting steps into one macro to automate a daily process. I do not want to go through daily and have to maually do this for multiple columns within a worksheet. So that's not going to work for me I don't think. Is there a way to incorporate that into a macro?
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is there a reason there needs to be a zero in an unused cell?
     
  5. shooozy

    shooozy Thread Starter

    Joined:
    Jan 28, 2011
    Messages:
    4
    Yes, I take this sheet and upload it into another system and it won't recognize a blank cell so it causes an error :(
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Here is the hard-code version. Just change the range of cells in the first line.

    Code:
    For Each vCell In Range("A1:A30").Cells
    If vCell.Value = "" Or IsEmpty(vCell) Then
    vCell.Value = 0
    End If
    Next vCell
    If you prefer to manually select the range first you can use the macro below instead.

    Code:
    For Each vCell In Selection.Cells
    If vCell.Value = "" Or IsEmpty(vCell) Then
    vCell.Value = 0
    End If
    Next vCell
    Rollin
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You don't say how you're determining "last active row". But if you use something like:

    LastActiveRow = Range("A" & Rows.Count).End(xlUp).Row

    you can fill the blanks in one hit with no looping, with (example):

    Range("B1:B" & LastActiveRow).SpecialCells(xlCellTypeBlanks) = "0"

    HTH :)
     
  8. shooozy

    shooozy Thread Starter

    Joined:
    Jan 28, 2011
    Messages:
    4
    Thank you all for your help!!
     
  9. 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/977540

  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