VBA code to edit a column based on if statements

Dan Nichol asked

on 2017-04-20

25 Comments

1 Solution

74 Views

Last Modified: 2017-04-24

Hi all,

I've been trying to automate my job through Excel due to a new implementation of Excel, after the business lost SharePoint

I'm trying to create a button that will change the column E to "Pending Closure" based on if Column D is equal to "Live" and column P is less than C71 on a different sheet. (C71 is today's date and is linked into a refresh button that I have, also that sheet is named "Dashboard")

Sheet name is "Cloud Change Tracker"

I would attach the document but it is DWP Classified/Restricted; though I could anonymise it if necessary

Thanks in advance for any help

Comment

Watch Question

View Solution Only

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

Is there only one cell who's value has to be changed based on those conditions or are we dealing with several?

Assuming the latter, this code will fire whenever a change is made to any cell in column D of your worksheet.
Place the code in the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
        If Target.Value = "Live" And Range("P" & Target.Row).Value < Sheets("Dashboard").Range("C71").Value Then
            Range("E" & Target.Row).Value = "Pending Closure"
        End If
    End If
End Sub

Open in new window

Dan Nichol

Author

Commented: 2017-04-20

Ideally what I'm trying to create is a button that edits Column D based on the if statements, overwriting it to say Pending Closure if the requirements are met

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

All cells in column D?

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

If so, you can use this code and connect it to your button.

Private Sub Demo()
    Dim rng As Range, cell As Range
    
    Set rng = Range("D" & Range("D" & Rows.Count).End(xlUp).Row)
    For Each cell In rng.Cells
        If cell.Value = "Live" And Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value Then
            Range("E" & cell.Row).Value = "Pending Closure"
        End If
    Next
End Sub

Open in new window

Dan Nichol

Author

Commented: 2017-04-20

When I tried running this I realised I said the wrong cell, its E, so I changed that

Private Sub Demo()
    Dim rng As Range, cell As Range
    
    Set rng = Range("E" & Range("E" & Rows.Count).End(xlUp).Row)
    For Each cell In rng.Cells
        If cell.Value = "Live" And Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value Then
            Range("E" & cell.Row).Value = "Pending Closure"
        End If
    Next
End Sub

Open in new window

But when I run it, it appears to do something but doesn't actually change the cells to Pending Closure, not sure what's wrong

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

First of all, line 7 should read:

cell.Value = "Pending Closure"

Open in new window

After line 6 add these lines:

MsgBox "Range("P" & cell.Row).Value = " & Range("P" & cell.Row).Value & vbcrlf & _
"Sheets("Dashboard").Range("C71").Value = " & Sheets("Dashboard").Range("C71").Value & vbcrlf & _
"Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value = " Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value

Open in new window

Dan Nichol

Author

Commented: 2017-04-20

Tried adding those lines after line 6 but I'm getting Compile Error, Expected: End of statement

It highlights as below

MsgBox "Range("P" & cell.Row)

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

Sorry my bad, it should read:

Private Sub Demo()
    Dim rng As Range, cell As Range
    
    Set rng = Range("D" & Range("D" & Rows.Count).End(xlUp).Row)
    For Each cell In rng.Cells
        If cell.Value = "Live" And Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value Then
            MsgBox """Range(""P"" & cell.Row).Value = """ & Range("P" & cell.Row).Value & vbCrLf & _
                   """Sheets(""Dashboard"").Range(""C71"").Value = """ & Sheets("Dashboard").Range("C71").Value & vbCrLf & _
                   """Range(""P"" & cell.Row).Value < Sheets(""Dashboard"").Range(""C71"").Value = """ & Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value
            cell.Value = "Pending Closure"
        End If
    Next
End Sub

Open in new window

Dan Nichol

Author

Commented: 2017-04-20

Tested that, it does appear to run but it didnt change any of the expected values

I did change like 4 to:

Set rng = Range("E" & Range("E" & Rows.Count).End(xlUp).Row)

Open in new window

But I'm not sure if that's the issue

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

What did the msgbox say?

Dan Nichol

Author

Commented: 2017-04-20

I think that's part of the problem, it didnt come up

I tested it both through assigning it to the button and from the macro panel (removed Private Sub for that test)

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

Try this:

Private Sub Demo()
    Dim rng As Range, cell As Range
    
    Set rng = Range("E" & Range("E" & Rows.Count).End(xlUp).Row)
    For Each cell In rng.Cells
            MsgBox """Range(""P"" & cell.Row).Value = """ & Range("P" & cell.Row).Value & vbCrLf & _
                   """Sheets(""Dashboard"").Range(""C71"").Value = """ & Sheets("Dashboard").Range("C71").Value & vbCrLf & _
                   """Range(""P"" & cell.Row).Value < Sheets(""Dashboard"").Range(""C71"").Value = """ & Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value
        If cell.Value = "Live" And Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value Then
            cell.Value = "Pending Closure"
        End If
    Next
End Sub

Open in new window

Dan Nichol

Author

Commented: 2017-04-20

Testing went a bit better, messagebox came up

Simply said "False"

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

Which means that
Range("P" & cell.Row).Value and Sheets("Dashboard").Range("C71").Value have different values...

Dan Nichol

Author

Commented: 2017-04-20

Yeah, they do

Range("P" & cell.Row).Value is the end date of that project

Sheets("Dashboard").Range("C71").Value is todays date

The point of this script is that, each morning the projects that have finished need to be moved from Live to Pending Closure

So Range("P" & cell.Row).Value needs to be earlier than today, as in the project has ended and now needs to be moved into pending closure, if that makes sense?

How can I get the script to do that?

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

Sorry, what I meant was, that Range("P" & cell.Row).Value is not being recognized as being earlier than Sheets("Dashboard").Range("C71").Value.

Try converting the values to dates:

Private Sub Demo()
    Dim rng As Range, cell As Range
    
    Set rng = Range("E" & Range("E" & Rows.Count).End(xlUp).Row)
    For Each cell In rng.Cells
        MsgBox """Range(""P"" & cell.Row).Value = """ & Range("P" & cell.Row).Value & vbCrLf & _
            """Sheets(""Dashboard"").Range(""C71"").Value = """ & Sheets("Dashboard").Range("C71").Value & vbCrLf & _
            """Range(""P"" & cell.Row).Value < Sheets(""Dashboard"").Range(""C71"").Value = """ & Range("P" & cell.Row).Value < Sheets("Dashboard").Range("C71").Value
        If cell.Value = "Live" And CDate(Range("P" & cell.Row).Value) < CDate(Sheets("Dashboard").Range("C71").Value) Then
            cell.Value = "Pending Closure"
        End If
    Next
End Sub

Open in new window

Dan Nichol

Author

Commented: 2017-04-20

False again,

If it helps, this is the format of the dates

24/02/2017 19:00
22/02/2017 08:00
04/02/2017 07:00
24/02/2017 08:00
25/02/2017 05:30

And Dashboard C71 is

20/04/2017 00:00

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-20

This returns true:

CDate("24/02/2017 07:00") < CDate("20/04/2017 00:00")

Open in new window

so I'm stumped. If you can upload a sample I'll take a look.

Dan Nichol

Author

Commented: 2017-04-21

Anonymised copy of the workbook:
Cloud-Change-Tracker-V2.xlsm

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-21

This seems to work:

Sub Pending_Closure()
    
    Dim rng As Range, cell As Range
    
    Set rng = Range("E3:E" & Range("E" & Rows.Count).End(xlUp).Row)
    For Each cell In rng.Cells
        If cell.Value = "Live" And CDate(Range("P" & cell.Row).Value) < Now Then
            cell.Value = "Pending Closure"
        End If
    Next
    
End Sub

Open in new window

The reason it didn't make any changes is because the lines where cell E is "Live" the dates in cell P are greater than Sheets("Dashboard").Range("C71").Value

24/04/2017 07:00
31/05/2017 17:00
03/09/2017 21:00
09/09/2017 21:00
08/09/2017 22:30

Dan Nichol

Author

Commented: 2017-04-23

When running that it seems to be doing the right things and doesn't cause any errors, but on the other test copy I have its just not overwriting the data

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-24

Unlock this solution and get a sample of our free trial.

(No credit card required)

UNLOCK SOLUTION

Dan Nichol

Author

Commented: 2017-04-24

Is the Option Explicit declaration important?

Joe Howard

CERTIFIED EXPERT

Commented: 2017-04-24

It forces variable declarations.
It is strongly recommended to include it in every module.
It can help you find bugs in your code.

Dan Nichol

Author

Commented: 2017-04-24

After a bit of playing around I finally got it working! I cant thank you enough for this help!

VBA code to edit a column based on if statements

Premium Content

Premium Content

View Solution Only

Thanks for using Experts Exchange.