Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    Replacing zero-length strings but skipping certain columns

    Hi folks,

    As a followup to an earlier thread (http://windowssecrets.com/forums/sho...-an-empty-cell), I'm trying to replace the zero-length strings our analysis software produces as output. My problem now is that I need to do it for every other column.

    In the attached workbook you'll see that columns A, C, E, etc. are labelled "User Annotation." I need those cells to remain blank (i.e. zero-length strings). However, when a cell in columns B, D, F, H, etc. is blank, I need to replace it with a 0.

    On the bright side: the columns are always the same in every output, thus I'm always replacing B, D, F, etc.

    On the dark side: the number of rows will change every time.

    Here's my current code:
    Code:
    Sub FillEmptyBlankCellWithValue()
    
    'Fill an empty or blank cell in selection
    'with value specified in InputBox
        
        Dim cell As Range
        
        On Error Resume Next
        
        'Test for empty cell. If empty, fill cell with value given
        
        For Each cell In Selection
            If cell.Value = "" Then 'look for zero-length strings
                cell.Value = "0.00"
            End If
        Next
        
        Selection.NumberFormat = "0.000"
    
    End Sub
    Currently I'm selecting the cells in the alternating rows, but it's painful & prone to user error. What's the best way to have the code skip the User Annotation columns?

    Thanks in advance!

    Beej
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Beej View Post
    Hi folks,

    As a followup to an earlier thread (http://windowssecrets.com/forums/sho...-an-empty-cell), I'm trying to replace the zero-length strings our analysis software produces as output. My problem now is that I need to do it for every other column.

    In the attached workbook you'll see that columns A, C, E, etc. are labelled "User Annotation." I need those cells to remain blank (i.e. zero-length strings). However, when a cell in columns B, D, F, H, etc. is blank, I need to replace it with a 0.

    On the bright side: the columns are always the same in every output, thus I'm always replacing B, D, F, etc.

    On the dark side: the number of rows will change every time.

    Here's my current code:
    Code:
    Sub FillEmptyBlankCellWithValue()
    
    'Fill an empty or blank cell in selection
    'with value specified in InputBox
        
        Dim cell As Range
        
        On Error Resume Next
        
        'Test for empty cell. If empty, fill cell with value given
        
        For Each cell In Selection
            If cell.Value = "" Then 'look for zero-length strings
                cell.Value = "0.00"
            End If
        Next
        
        Selection.NumberFormat = "0.000"
    
    End Sub
    Currently I'm selecting the cells in the alternating rows, but it's painful & prone to user error. What's the best way to have the code skip the User Annotation columns?

    Thanks in advance!

    Beej
    If you just want to process alternate columns of the selection differently, then you can check the column number of the cell variable to see whether it is divisible by 2 or not, using the mod operator which returns the remainder after division by a specified number.

    E.g. around your existing If statement, put a test like this:

    If cell.column mod 2 = 0 then ' this is true for when cell.column = 2,4,6,8 etc i.e. columns B,D,F,..
    ' existing code
    end if

    or
    If cell.column mod 2 = 1 then ' this is true for when cell.column = 1,3,5, 7 etc i.e. columns A,C,E,...
    ' existing code
    end if

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    That is a neat way for testing for columns.
    But for faster processing, you can also use the vba Step 2 to increment in twos (for example).

    I have attached my version of the file with a vba routine that puts a zero in the required cells.
    Also, I made the cell colour pale blue to show which cells were modified
    This makes checking easier.
    Also, when done, it is easy to select data rows and remove the pale blue backgrounds if wanted, by choosing No Fill etc.

    Run the routine "FillEmptyBlankCellWithZero" in the attached file to see.

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    Beej (2013-03-11)

  5. #4
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    zeddy,

    Thanks. That's exactly what I needed. I was going down a path similar to jeremybarker's originally, but it was doing every cell in the column regardless of whether it had content. The code you've provided here is what I had in mind but didn't have the programming experience to implement.

    Again, you folks are a tremendous resource. Thanks for all you do!

    Beej

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Beej

    Glad to help.
    Have you looked at my reply#9 and attached file re: Recreating a data display table using VBA

    zeddy

Posting Permissions

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