Announcement

Collapse
No announcement yet.

Enter Text Into Blank Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

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

    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 :

    Code:
    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.
    Templates & Calculators

    Comment


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

      I wouldn't recommend doing a whole sheet like that.
      Code:
      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

      Comment


      • #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.
        Code:
        'Set WS = Worksheets("Sheet2") 
        'BottomRowPos = WS.Range("B65536").End(xlUp).Row
        Best,
        ALATL

        Comment


        • #5
          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.
          .

          Comment


          • #6
            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.

            Comment


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

              Code:
              Dim cl as Range
              For Each cl in Selection
                 If cl = "" Then cl = "Empty"
              Next cl
              lenze

              Comment


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

                Hi,

                Selection.SpecialCells(xlCellTypeBlanks).Value = "Empty"
                Kris

                ExcelFox

                Comment


                • #9


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

                  Originally posted by Kenneth Hobson
                  I wouldn't recommend doing a whole sheet like that.
                  Code:
                  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.
                  Templates & Calculators

                  Comment

                  Working...
                  X