asked on

VBA code to edit a column based on if statements

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

Joe Howard

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

ASKER

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

All cells in column D?

Joe Howard

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

ASKER

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

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

ASKER

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

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

ASKER

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

What did the msgbox say?

Dan Nichol

ASKER

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

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

ASKER

Testing went a bit better, messagebox came up

Simply said "False"

Joe Howard

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

Dan Nichol

ASKER

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

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

ASKER

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

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

ASKER

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

Joe Howard

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

ASKER

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

ASKER CERTIFIED SOLUTION

Joe Howard

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dan Nichol

ASKER

Is the Option Explicit declaration important?

Joe Howard

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

ASKER

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