We help IT Professionals succeed at work.

VBA code to edit a column based on if statements

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

CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
All cells in column D?
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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)
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
What did the msgbox say?

Author

Commented:
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)
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Testing went a bit better, messagebox came up

Simply said "False"
CERTIFIED EXPERT

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

Author

Commented:
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?
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

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

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Is the Option Explicit declaration important?
CERTIFIED EXPERT

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

Author

Commented:
After a bit of playing around I finally got it working! I cant thank you enough for this help!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.