0

In my workbook "isum", I have the week number figured out by a WEEKNUM formula (right now is week 27) listed on column X under the label Week#. The worksheet is called "Orders" with data to see what orders are late. I am struggling to create an if/then statement that makes it so that all of the week numbers on column X (starting at X2) that are < 27 (the current week number out of 52) are labeled as "Late". I am not sure how to change this value to the text, but the hard part is making sure that each week changes until it gets to 52. Otherwise nothing will change that is "Late". If this does not make sense let me know, but this is what I have so far:

isum.Sheets("Orders").Activate Range("X2").Formula = "=WEEKNUM(RC[-9])" Range("X2", "X" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown 'Change statement to say "Late" and account for changing week numbers after every week If cell.Value < 27 Then cell.Value = "Late"

example of dilemma

  • Do you really want to remove the week number in favor of text or would you prefer to disguise the number to show "Late" while retaining the underlying value? –  Jul 05 '18 at 22:44
  • I guess it wouldn't matter, either way it is going to a pivot table where it would show the data until anything that is "Late" rather than a week number. So, the actual number is not going to be used if it is "Late". –  Jul 05 '18 at 23:14

2 Answers2

0

Try looping through the range

Dim col As Range: Set col = Worksheets("Orders").Range("X2:X" & <current week num>)
Dim i As Integer
For i = 1 To col.Rows.Count
    col.Cells(RowIndex:=i, ColumnIndex:="X").Value = "Late"
Next

(http://codevba.com/excel/for_each_cell_in_range.htm)

nathanscain
  • 121
  • 5
0

I would suggest a custom number format that displays Late for weeknums less than 27 but retains the underlying numerical weeknum value for use in future calculations. This can be applied through a conditional formatting rule that checks the weeknum formula's result against the current weeknum for dynamic results week to week.

With isum.workSheets("Orders")
    With .Range(.Cells(2, "X"), .Cells(.Rows.Count, "O").End(xlUp).Offset(0, 9))
        .Formula = "=weeknum(o2)"
        .NumberFormat = "0_)"
        .FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=x2<weeknum(today())")
            .NumberFormat = "L\at\e_)"
            'optionally apply a red fill color
            '.interior.color = vbred
        End With
    End With
End With
  • Would this be rotating the weeks as the code runs through each week? i.e. next week is week number 28 so 27 would then be considered "Late" –  Jul 05 '18 at 23:21
  • What happens when you change your computer's date? –  Jul 05 '18 at 23:36
  • My computers date was off! So simple yet so important. Your code works and thank you for the optional red fill color. –  Jul 06 '18 at 16:10
  • A question: If I wanted to say "Past Due" instead of "Late", how would I enter that? I guess I don't know how to change the \ \. –  Jul 06 '18 at 16:19
  • The backslash is an escape character so that characters like *D* (as in **D**ue) get treated as a string literal instead of a day. Personally, I usually just backslash everything since I once got caught on leaving [an unescaped *b*](https://stackoverflow.com/questions/32804557/what-does-bb-or-bbbb-represent-in-an-en-us-text-function-or-custom-number-format) in the format mask. So use `.NumberFormat = "\P\a\s\t \D\u\e_)"` and let Excel figure out whether everything has to be escaped or not. –  Jul 06 '18 at 18:49
  • Coming back to this thread, I have one dilemma with the output. I want the same outcome however ALL the weeks are listed as "Late" because the week numbers before the current week are all listed as late. Is there a way to keep the same code but not include the 2019 week numbers as "Late"? I edited the post to include an example picture above, where the "week #" has the WEEKNUM formula and I am using your exact code. –  Jul 09 '18 at 15:58