Improve code for excel button

[Inuniform]

Hi everybody,

I was hoping you could help me with the following problem. Currently I am working on a register system and for that I have a button that if the current date (J9 with =Today function) exceeds the due date of a particular row in Column F that the status in column D changes to "Behind". The problem is that the code changes all statusses to behind in column D, even if a row has no content at all. Is there a possibility to change the code so it only changes the statuses to "behind" if there is something written in for example column A? The code I use is the following:

Sub Button2()
    Dim lr As Long, i As Long
    lr = Range("F" & Rows.Count).End(xlUp).Row
    For i = 13 To 40
        If Range("F" & i) < Range("J9") Then
            Range("D" & i) = "Behind"
        End If
    Next i
End Sub

Thanks in advance!

Inuniform

 

[RCBricker]

you are checking if the date in "F" & I is greater than today's date but your code compares "F"&i and is checking if it is less than today's date

also you don't need to compare it to a cell with today's date just use date

If Range("F" & i) > date Then

 

[Inuniform]

Thanks for your reply RCBricker!

Sorry I have written it wrong in the in my initial question. It should be that if the current date exceeds the due date, then the status in column D should change to behind. However, I have tried your code as in

If Range("F" & i) < date Then

but now it changes all statusses in column D to behind, even if todays date does not exceed the due date. It does only take the the rows in which something is written though so that problem is solved.

 

[RCBricker]

can I see an example of your data?

Also why is your loop from 13 to 40? You take the time to set lr to the last row so why not 13 to lr?

Also you were correct on the signage and I was wrong. you had it correct from the beginning. This is what happens before coffee.

 

[Inuniform]

Allright so if I do

If Range("F" & i) < Date Then

then it changes the status in all rows where no dates are to behind, and if i use

If Range("F" & i) > Date Then

it changes everything to behind if there is a date. I really don't understand why...

 

[Inuniform]

Haha no worries, take your time and enjoy your coffee. allright here it comes:

Row Nr

18

ID

Task

Affects the output

Status

Priority

Due Date

Requested by

Start Date

Assigned to:

Estimated time (h)

Category

Current date

Actual time

1

Test

yes

Not Started

High

15/11/2016

Person x

17-Nov-16

Person y

01/12/2016

12

test

no

Behind

High

12-Dec-16

Person x

18-Nov-16

Person y

2.00

01/12/2016

15

test

no

In progress

High

24/11/2016

Person x

23-Nov-16

Person y

2.00

01/12/2016

17

test

no

Behind

High

24/11/2016

Person x

23-Nov-16

Person y

2.00

Work assignment

01/12/2016

18

Mosselen

no

Behind

High

03/12/2016

Person x

1-Dec-16

Person y

2.00

Work assignment

01/12/2016

<tbody>
</tbody>

The reason why I have 13 to 40 is because last time it changed all rows to behind and excel jammed. If the code works and only changes the statusses to behind when there is an actual task in the row, then it could be changed.

 

[Inuniform]

Allright I figured it out! I changed the code to:

Sub Button2()
    Dim lr As Long, i As Long
    Dim Tmp As Date
    lr = Range("F" & Rows.Count).End(xlUp).Row
    For i = 13 To 40
        Tmp = CDate(Range("F" & i).Value)
        If Format(Range("F" & i).Value, "yyyy-mm-dd") < Format(Date, "yyyy-mm-dd") And Range("F" & i).Value <> "" Then
            Range("D" & i) = "Behind"
        End If
    Next i
End Sub

And now it works

Thanks for your effort! Have a great day!

 

[RCBricker]

Allright I figured it out! I changed the code to:

Sub Button2()
    Dim lr As Long, i As Long
    Dim Tmp As Date
    lr = Range("F" & Rows.Count).End(xlUp).Row
    For i = 13 To 40
        Tmp = CDate(Range("F" & i).Value)
        If Format(Range("F" & i).Value, "yyyy-mm-dd") < Format(Date, "yyyy-mm-dd") And Range("F" & i).Value <> "" Then
            Range("D" & i) = "Behind"
        End If
    Next i
End Sub

And now it works

Thanks for your effort! Have a great day!

based on your example

All of the line items should be behind except the last one. that said, I changed the status for each line item to either not started or in progress. They changed to behind. Changed them back and made all dates to fall in 2017, they did not change. Add more lines and changed all status to not started and randomly set some to 2015, 2016, and 2017. All dates prior to 12/01/2016 (today's date) changed to behind. All others remained the same. Here is your code with an IF statement to see if the cell in Column A has data (if not do nothing, else rest of your code), Using your lr variable to find the last row of data and finally also using the date function rather than a cell.

Sub Button2()
    Dim lr As Long, i As Long
    lr = Range("F" & Rows.Count).End(xlUp).Row
    For i = 13 To lr
        If Not Range("A" & i).Value = "" Then
            If Range("F" & i) < Date Then
                Range("D" & i) = "Behind"
            End If
        End If
    Next i
End Sub

TLDR
your code works as intended on my machine using your data and further testing with dummy data.

 

[Inuniform]

Thanks alot! I have tried your code and it works perfectly. I have made a new thread for this but since you are familiar with my data you might be able to answer this. (I can imagine that I'm not supposed to make a new thread since it is almost the same question and you are already helping me, but I am working with a deadline and you were offline so I was stressing a bit ) As you know In column A every task gets an ID tag. I have made a control panel where people can type in an ID in K4 and when they push the button the status in column D for the row that matches the ID in column A should change to "Completed".

If you could help me out with this one too you are my hero of the year 2016!

 

[Inuniform]

Nevermind it is already working! Everything is working fine in my workbook now. Thank you so much!