# If Macro

#### Johnstog

##### Board Regular
I need to include this into a macro, would someone be able to help me.

1) if column R is "" (this one I actually am able to do a macro) then U = "0"
2) if column R has ANY data in that column then the corresponding column U = "1"

Thanks for any help you may be able to assist me with.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try

Code:
``````Sub ColR()
Dim LR As Long, i As Long
LR = Range("R" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("R" & i).Value = "" Then
Range("U" & i).Value = 0
Else
Range("U" & i).Value = 1
End If
Next i
End Sub``````

In a formula in cell U1:

=IF(R1="",0,1)

...and copy down the column. This will work in real-time, no need to run a macro or remember to.

In a macro:
Code:
``````Sub ColumnRTest()
Dim cell As Range, rng As Range
Dim lastrow As Long

lastrow = ActiveSheet.UsedRange.Rows.Count
Set rng = ActiveSheet.Range("R1:R" & lastrow)

For Each cell In rng
If cell.Value = "" Then
Range("U" & cell.Row).Value = 0
Else
Range("U" & cell.Row).Value = 1
End If
Next cell
End Sub``````

try this

Code:
``````Sub CheckR()
Dim i As Long, LR As Long
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LR
If Cells(i, 18) = "" Then
Cells(i, 19) = 0
End If
If Cells(i, 18) <> "" Then
Cells(i, 19) = 1
End If
Next i
End Sub``````

That works, thanks!

Secondly, Is there a way that I can get Excel to clear the cells in columns O:R if the value = midnight. These cells will have a date in front of them but if the time = "12:00:00 AM"?

Try

Code:
``````Sub NoMdnt()
Dim LR As Long, i As Long, c As Range
Dim Mdnt As Date
Mdnt = TimeValue("00:00:00")
LR = Range("R" & Rows.Count).End(xlUp).Row
For Each c In Range("O1:R" & LR)
If IsDate(c.Value) Then
If TimeValue(c.Value) = Mdnt Then c.ClearContents
End If
Next c
End Sub``````

I am not having much luck with this one.

The value in these columns will look like this:
"2/11/2009 10:42:14 AM"

I need the cell cleared anytime it finds an exact midnight value, like:
"2/11/2009 12:00:00 AM" or
"2/12/2009 12:00:00 AM" etc.

It doesn't matter what date is in front, if the time part of the value equals midnight "12:00:00 AM" in columns O thru R, the cell should be cleared.

This works for me, as did the previous one:

Code:
``````Sub NoMdnt()
Dim LR As Long, i As Long, c As Range
Dim Mdnt As Date
Mdnt = TimeValue("12:00:00 AM")
LR = Range("R" & Rows.Count).End(xlUp).Row
For Each c In Range("O1:R" & LR)
If IsDate(c.Value) Then
If TimeValue(c.Value) = Mdnt Then c.ClearContents
End If
Next c
End Sub``````

I'll play with it some more, unsure why it isn't working for me.

Maybe, you can assist me with this one. I apologize if it is confusing:

In Column M I have a list of times, which I have downloaded from another program to Excel. For some strange reason not all of the data has a date with the time. I have to manually correct these.

columns N thru S, I have a macro to change theses columns to to text, then 'paste special' + 'Add' a date copied from the date in cell B1. I did not include column M:M in this part of my macro because there is already dates on some of the data. I would like Excel to go though this column and 'Paste Special' & 'Add' the date copied from cell B1 only to the cells that don't have a date.

Here is what is looks like Before:
10:52:22
11:22:05
12:57:19
2/14/2009 13:03:33
2/14/2009 13:36:18
2/14/2009 13:40:13
13:41:19
2/14/2009 13:43:10
14:06:22

Also, know that all this data is downloaded as text. So, if I change the format to date & time those without a date will appear as "01/01/1900 10:52:22"

Any suggestions? Maybe if I format everything in this column to date & time and replace the date with the date in B1 with any date that = "01/01/1900"

If they are text values that explains why the macros didn't work. Try

Code:
``````Sub NoMdnt()
Dim LR As Long, i As Long, c As Range
LR = Range("R" & Rows.Count).End(xlUp).Row
For Each c In Range("O1:R" & LR)
If c.Text Like "*12:00:00 AM*" Then c.ClearContents
Next c
End Sub``````

Replies
7
Views
185
Replies
21
Views
811
Replies
1
Views
144
Replies
7
Views
417
Replies
2
Views
203

1,196,385
Messages
6,014,974
Members
441,860
Latest member
Store154

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

### Which adblocker are you using?

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

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