If Macro

Johnstog

Board Regular
Joined
Sep 11, 2007
Messages
163
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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