Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Enter Text Into Blank Cells

  1. #1
    Join Date
    29th November 2006
    Posts
    4

    Enter Text Into Blank Cells

    As simple as this sounds, I can't seem to make this work. I am looking for
    code for the following:

    I would like to enter the word "Empty" into a cell if the cell is blank. The
    range can be variable. The only columns that contain an empty cell are
    columns B or C which can have any number of rows.

    I appreciate any suggestions.

    Best.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    30th November 2005
    Location
    England
    Posts
    131

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    Quote Originally Posted by ALATL
    As simple as this sounds, I can't seem to make this work. I am looking for
    code for the following:

    I would like to enter the word "Empty" into a cell if the cell is blank. The
    range can be variable. The only columns that contain an empty cell are
    columns B or C which can have any number of rows.

    I appreciate any suggestions.

    Best.
    How about :

    VB:
    Sub Replace_Blanks() 
         
        Application.Goto Reference:="WHATEVER" 
        Selection.Replace What:="", Replacement:="EMPTY", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
         
    End Sub 
    
    
    Replace "WHATEVER" with your range. Hope this helps.

  3. #3
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,081

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    I wouldn't recommend doing a whole sheet like that.
    VB:
    Option Explicit 
    Sub RangeSetEmpty() 
        Replace_Blanks [A1:A10], "Empty" 
    End Sub 
     
    Sub Replace_Blanks(rangeToEmpty As Range, replacementText As String) 
        Dim cell As Range 
        For Each cell In rangeToEmpty 
            If cell.Value = "" Then cell.Value = replacementText 
             'Application.Goto Reference:=rangeToEmpty
        Next cell 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    29th November 2006
    Posts
    4

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    Kenneth,

    I see where you have hard coded the range values of "A1:A10". However, I will have a variable number of rows each time the apllication because the data set is different every time.

    I have seen something like this used to determine the last row of data in a range.
    VB:
     'Set WS = Worksheets("Sheet2")
     'BottomRowPos = WS.Range("B65536").End(xlUp).Row
    
    
    Best,
    ALATL

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    ALATL, Please read the rules and use code tags round VBA code. I have added them for you this time, but normally the post would be deleted.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,081

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    Right. The first sub is an example. Obviously, if you want to do this for a sheet that is not active, you also have to pass the sheet name to the 2nd Sub and Activate the sheet in that sub or some other similar method. You can make the first sub Activate some sheet first if you like.

    Since you are wanting to mark the empty cells, some methods might not work for you. Look into using the UsedRange property.

    You can use various methods to build your Range to pass to the first Sub. This forum has some good tips in the FAQ section to find the last used cell, row, column, etc.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    27th January 2003
    Location
    Montana
    Posts
    204

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    VB:
    Dim cl As Range 
    For Each cl In Selection 
        If cl = "" Then cl = "Empty" 
    Next cl 
    
    
    lenze

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,254

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    Hi,

    VB:
    Selection.SpecialCells(xlCellTypeBlanks).Value = "Empty" 
    
    

  9. #9
    Join Date
    30th November 2005
    Location
    England
    Posts
    131

    Re: Enter The Word "empty" Into A Cell If The Cell Is Blank

    Quote Originally Posted by Kenneth Hobson
    I wouldn't recommend doing a whole sheet like that.
    VB:
    Option Explicit 
    Sub RangeSetEmpty() 
        Replace_Blanks [A1:A10], "Empty" 
    End Sub 
     
    Sub Replace_Blanks(rangeToEmpty As Range, replacementText As String) 
        Dim cell As Range 
        For Each cell In rangeToEmpty 
            If cell.Value = "" Then cell.Value = replacementText 
             'Application.Goto Reference:=rangeToEmpty
        Next cell 
    End Sub 
    
    
    I wasn't suggesting to search the whole sheet, only the pre-defined range.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Sort Blank Cells (Empty Text) To Bottom
    By browndt1 in forum EXCEL HELP
    Replies: 4
    Last Post: August 7th, 2008, 11:21
  2. Text To Columns Macro With Blank Cells
    By mcurry74 in forum EXCEL HELP
    Replies: 2
    Last Post: January 4th, 2008, 11:39
  3. Dynamic Range For Text & Blank Cells
    By Upside in forum EXCEL HELP
    Replies: 2
    Last Post: December 10th, 2007, 05:40
  4. IF function...merge cells & enter text
    By cochese in forum EXCEL HELP
    Replies: 6
    Last Post: July 1st, 2006, 09:37
  5. Enter Data Into Next Blank Cell
    By The Doll in forum EXCEL HELP
    Replies: 9
    Last Post: October 29th, 2004, 03:30

Bookmarks

Posting Permissions

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