Add "" if cell blank

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings I have the following VBA, I need another component to show if there is nothing or a blank cells there will be nothing added. The formula below is still adding 0000 to an empty cell.
VBA Code:
Sub twenty_four_hour_Clock()
With Range("D1", Range("D" & Rows.Count).End(xlUp))
x = .Address

.Value = Evaluate("if(len(" & x & ")=3,""0""&" & x & "," & x & ")")
.Value = Evaluate("if(len(" & x & ")=2,""00""&" & x & "," & x & ")")
.Value = Evaluate("if(len(" & x & ")=1,""000""&" & x & "," & x & ")")
End With
End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,754
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub twenty_four_hour_Clock()
With Range("D1", Range("D" & Rows.Count).End(xlUp))
   x = .Address
   .Value = Evaluate("if(" & x & "="""","""",text(" & x & ",""0000""))")
End With
End Sub
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
How about
VBA Code:
Sub twenty_four_hour_Clock()
With Range("D1", Range("D" & Rows.Count).End(xlUp))
   x = .Address
   .Value = Evaluate("if(" & x & "="""","""",text(" & x & ",""0000""))")
End With
End Sub
Thank you! I have been working on trying to sort the times out for the last couple of weeks, and thought maybe I'm making more of a meal of this then necessary. I've included an image of times and their text counterparts. What code could I use to isolate just the time? It would have to be the 24 hour clock with no colon and include zeros in front of the number such as 0005, 0325 etc. I tried something like (see below) just to experiment, and it left me with 0000 for each item. If I can get the times this way I think it will save a lot of extra macros including the one you just helped me with. Thank you, so much!

VBA Code:
Sub TIME()

Range("D13") = Format(Date, "HHMM")


End Sub




time code.JPG
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,754
Office Version
  1. 365
Platform
  1. Windows
As this is a different question, then you will need to start a new thread.
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Got it! and that is what I just did.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,077
Messages
5,628,516
Members
416,323
Latest member
65563

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
Top