Insert specific text in blank cell with VBA

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings, in Column E(5) of worksheet 72 HR I would like “TBD” entered in blank cells. Column D(4) will determine how many rows I need to go down to. In other words any data in Column D then TBD will be placed in Column E. Thank you.
 
Oops, forgot to add something.
Point these two formulae at one of the "blank" cells in col D, what do they both return
Excel Formula:
=isblank(D2)
=len(d2)
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You're right I clicked on the little erasure to clean those cells up then it worked properly. I don't understand. I do clear out all contents prior before each paste, see first attachment. I do have to cut a huge database and I have around 30 macros for this. It works great except this last one (the one we're working on). I tried using my hidden value macro (second macro attached) and nothing happened. I think that is primarily for data one would get off the internet. Do you have any recommendations?

VBA Code:
Sub Clear_Schedule()
Dim ws As Worksheet
For Each ws In Sheets(Array("72 Hr", "Outbound FIDS"))
ws.Cells.Clear
ws.Cells.RowHeight = 14.4
ws.Cells.ColumnWidth = 8.11
Next ws
End Sub


Clear hidden values

VBA Code:
Sub remove_hidden_Values()
Dim cell As Range
    For Each cell In Selection
        cell.Value = Trim(Replace(cell.Value, Chr(160), Chr(32)))
    Next
End Sub
 
Upvote 0
Oops, forgot to add something.
Point these two formulae at one of the "blank" cells in col D, what do they both return
Excel Formula:
=isblank(D2)
=len(d2)
For =isblank(D2) I get False
For =len(d2) I get 0

Sorry I didn't see that response. I should point out in an earlier macro when I was deleting the date from the time, it also remove the Roll in "Roll Call", so I had another macro to insert the Roll back in. I couldn't figure out how to leave "Roll Call" alone in the first VBA. Could that be the source of the problem? It achieved what I needed to despite having to use an extra macro. You will notice from the images Roll Call shares the same Column as my times. I provided those two VBAs for better clarity.


VBA Code:
Sub IsolateTime()
Dim cel As Range
With Sheets("72 Hr").Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    For Each cel In .Cells
        cel.Value = Format(Right$(cel.Text, 4), "@")
    Next cel
    .NumberFormat = "@"
End With
End Sub
Sub addTextAtBeginningCell()
    Dim R As Range
    With Sheets("72 Hr").Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    For Each R In .Cells
        If R.Value = "CALL" Then R.Value = "ROLL " & R.Value
    Next
    End With
End Sub
 
Upvote 0
How about
VBA Code:
Sub Livin()
   With Sheets("72 HR")
      With .Range("D5", .Range("D" & Rows.Count).End(xlUp))
         .Value = .Value
         .SpecialCells(xlConstants).Offset(, 1).SpecialCells(xlBlanks).Value = "TBD"
      End With
   End With
End Sub
 
Upvote 0
Solution
Hi, I gave it ago, and I'm afraid that did not work either. The second one you provided was the closest since it worked after I cleared the blanks (at blank in appearance). How about a macro that clears the contents of Column D with the exception of "ROLL CALL" and a four digit number in text format? Then I can use your Macro and it should be good. Thank you,
 
Upvote 0
We got your macro to work, we put in the macro in front of yours to sort out that column. It's wonderful. Thank you so much!

VBA Code:
Sub DoNotContainrollcall()
Dim cell As Range
  For Each cell In Sheets("72 HR").Range("D2", Range("D" & Rows.Count).End(xlUp))
    If cell.Value <> "ROLL CALL" And Len(cell) <> 4 Then
        cell.Clear
    End If
  Next cell
End Sub
 
Upvote 0
You could have added this to the original thread

VBA Code:
Sub MM1()
Dim cell As Range
  For Each cell In Sheets("72 HR").Range("D2", Range("D" & Rows.Count).End(xlUp))
    If cell.Value <> "ROLL CALL" And Len(cell) <> 4 Then
        cell.Clear
       cell.offset(0,1).value = "TBD"
    End If
  Next cell
End Sub
 
Upvote 0
You could have added this to the original thread

VBA Code:
Sub MM1()
Dim cell As Range
  For Each cell In Sheets("72 HR").Range("D2", Range("D" & Rows.Count).End(xlUp))
    If cell.Value <> "ROLL CALL" And Len(cell) <> 4 Then
        cell.Clear
       cell.offset(0,1).value = "TBD"
    End If
  Next cell
End Sub
I'll have a look at it when I come in tomorrow. Thank you so much for your help.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top