populate values in a cell based on date criteria

M

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
Hi All,

I required assistance on the below. Would be great if someone could help me out with it.

I have the below file.

https://www.dropbox.com/s/5wzide7bso...%203.xlsx?dl=0

I have to look at the below columns
I - Usage , column L - Batch creation Date , M - Batch Expiry Date and N - Inventory Flag

There is a coulmn name "Usage - I" which has the below 7 categories data . So 1st case we need to filter first by "Unrestriced use" and "Unrestricted-Use Mat" and then need to filter column "M - Batch Expiry Date". So if the expiry date falls 12 months after the the current month i.e 2020 June onwards then column N should be polpulated as"Usable (>12)", if expiry date falls between 7 - 12 months after expiry date
which is from December 2019 - May 2020 then column N should be polpulated as"Usable (7-12)" . Under "Unrestriced use" and "Unrestricted-Use Mat" anything before May 2019 that is current month should be populated as
"Expired" and if expiry date is from " May 2019 - November 2019" then column N should be poulated as "Near expiry".
Next we need to filter by "Blocked Stock" under column I and if it is Blocked stock then column N should be populated as Blocked irrespective of the expiry date column - M . Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".Quality inspection - to be populated as "Quality inspection" under column N irrespective of expiry date
Valuated Goods Receipt Blocked Stock- to be populated as "Blocked" under column N irrespective of expiry date




1 Unrestricted Use
2. Transit
3.Blocked Stock
4.Unrestricted-Use Mat
5. Intransit
6.Quality inspection
7.Valuated Goods Receipt Blocked Stock


When " Batch Expiry Date" is blank or # we need to look at the "Batch Creation Date" column which is column "L" and the same rules apply. Exactly the same as expiry date rules except this goes 1 year back.


which is June 2018 onwards - Usable (>12)
Dec 2017 - May 2018 - Usable (7-12)
May 2017- Nov 2017 - Near Expiry
Before May 2017 -




If Manufacturing date and expiry date both are blanks or # present in both then N column should be poulated as Usable > 12.


Let me know how can we code the above and automate this procedure.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Check the following macro, maybe some dates do not fit in the filter but we will adjust it until it is ready.
I found this: In the "I" column you have this "Restricted-Use" data, it does not come in your filters.
Test and tell me.

Code: 
Sub populate_values()
    Dim sh As Worksheet, wDate As String, wDat2 As String, lr As Long, lr2 As Long
    Set sh = Sheets("base")
    
    Application.ScreenUpdating = False
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date) + 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Blocked Stock"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Blocked"


    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Transit", Operator:=xlOr, Criteria2:="=CC In-Transit"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Transit"


    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Quality Inspection"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Quality Inspection"
    
    '****
    'When " Batch Expiry Date" is blank or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date) - 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
        
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
        
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 1, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 2, Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    '****
    'If Manufacturing date and expiry date both are blanks or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    sh.Range("B1").AutoFilter Field:=11, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable > 12"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    MsgBox "Done"


End Sub
 
Upvote 0
Hi Dante,

Thanks for looking into above.

I tested it. The dates which were not getting populated had different format and hence were not fitting in filter. I fixed that.Sorry about not mentioning about the criteria " "Restricted-Use"
"Restricted-Use" should get populated as "Restricted" irrespective of Batch Expiry Date . I added that in your above code and it works fine .

I noticed the below mismatches.

"6/1/2020" is getting populated as "Usable (7-12)" instead of "Usable (>12)"


5/31/2019 , 6/1/2019, 5/1/2019 are getting populated as "Expired" instead of "Near Expiry".


When the usage is "Blocked Stock" Column N is being populated as "Expired" instead of "Blocked". These are for cases where Batch Expiry date is "#". I guess its getting populated on the basis of the code which is run later where Expiry date is "#". It should appear as Blocked irrespective of Expiry date when Usage is " Blocked Stock".

Rest the code works fine. Thank you so much once again
 
Upvote 0
Also i missed out on 2 more categorys that is "CC In-Transit". So alongwith "Transit and Intransit" , "CC In-Transit" should be populated as "Transit".

And the other is "Stock Transfer". It will always be populated as "Usable (>12)" irrespective of Batch Expiry Date.
 
Upvote 0
mogss_04 said:
Hi Dante,

Thanks for looking into above.

I tested it. The dates which were not getting populated had different format and hence were not fitting in filter. I fixed that.Sorry about not mentioning about the criteria " "Restricted-Use"
"Restricted-Use" should get populated as "Restricted" irrespective of Batch Expiry Date . I added that in your above code and it works fine .

I noticed the below mismatches.

"6/1/2020" is getting populated as "Usable (7-12)" instead of "Usable (>12)"


5/31/2019 , 6/1/2019, 5/1/2019 are getting populated as "Expired" instead of "Near Expiry".


When the usage is "Blocked Stock" Column N is being populated as "Expired" instead of "Blocked". These are for cases where Batch Expiry date is "#". I guess its getting populated on the basis of the code which is run later where Expiry date is "#". It should appear as Blocked irrespective of Expiry date when Usage is " Blocked Stock".

Rest the code works fine. Thank you so much once again
Click to expand...

As I mentioned, it is possible that there were some date errors in the filters.


Help me with the changes and perform the tests, for example, for this date
"6/1/2020" is getting populated as "Usable (7-12)" instead of "Usable (> 12)"


The code is in this part:


Code: 
[/COLOR][COLOR=#FF0000]    sh.Range("B1").AutoFilter Field:=12, Criteria1:="[/COLOR][SIZE=3][B][COLOR=#0000ff]>=[/COLOR][/B][/SIZE][COLOR=#FF0000]" & wDate, Criteria2:="[/COLOR][B][SIZE=3][COLOR=#0000ff]<=[/COLOR][/SIZE][/B][COLOR=#FF0000]" & wDat2[/COLOR]
[COLOR=#FF0000]    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _[/COLOR]
[COLOR=#FF0000]        sh.Range("N2:N" & lr).Value = "Usable (7-12)"[/COLOR]
[COLOR=#ff0000]

Only change the symbol to increase or decrease a day. For example

Code: 
[/COLOR][COLOR=#FF0000]    sh.Range("B1").AutoFilter Field:=12, Criteria1:="[/COLOR][SIZE=3][B][COLOR=#0000ff]>[/COLOR][/B][/SIZE][COLOR=#FF0000]" & wDate, Criteria2:="<=" & wDat2[/COLOR]
[COLOR=#FF0000]    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _[/COLOR]
[COLOR=#FF0000]        sh.Range("N2:N" & lr).Value = "Usable (7-12)"[/COLOR]
[COLOR=#FF0000]

Or

Code: 
[/COLOR][COLOR=#FF0000]    sh.Range("B1").AutoFilter Field:=12, Criteria1:="[/COLOR][COLOR=#ff0000]>=[/COLOR][COLOR=#FF0000]" & wDate, Criteria2:="[/COLOR][SIZE=3][B][COLOR=#0000ff]<[/COLOR][/B][/SIZE][COLOR=#FF0000]" & wDat2[/COLOR]
[COLOR=#FF0000]    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _[/COLOR]
[COLOR=#FF0000]        sh.Range("N2:N" & lr).Value = "Usable (7-12)"[/COLOR]
[COLOR=#FF0000]

Or

Code: 
[/COLOR][COLOR=#FF0000]    sh.Range("B1").AutoFilter Field:=12, Criteria1:="[/COLOR][B][COLOR=#0000FF]>[/COLOR][/B][COLOR=#FF0000]" & wDate, Criteria2:="[/COLOR][SIZE=3][B][COLOR=#0000FF]<[/COLOR][/B][/SIZE][COLOR=#FF0000]" & wDat2[/COLOR]
[COLOR=#FF0000]    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _[/COLOR]
[COLOR=#FF0000]        sh.Range("N2:N" & lr).Value = "Usable (7-12)"[/COLOR]
[COLOR=#FF0000]

---
Code: 
Sub populate_values()
    Dim sh As Worksheet, wDate As String, wDat2 As String, lr As Long, lr2 As Long
    Set sh = Sheets("base")
    
    Application.ScreenUpdating = False
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
[COLOR=#ff0000]    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="<=" & wDat2[/COLOR]
[COLOR=#ff0000]    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _[/COLOR]
[COLOR=#ff0000]        sh.Range("N2:N" & lr).Value = "Usable (7-12)"[/COLOR]
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date) + 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Blocked Stock"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Blocked"




    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Transit", Operator:=xlOr, Criteria2:="=CC In-Transit"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Transit"




    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Quality Inspection"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Quality Inspection"
    
    '****
    'When " Batch Expiry Date" is blank or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date) - 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
        
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
        
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 1, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 2, Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    '****
    'If Manufacturing date and expiry date both are blanks or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    sh.Range("B1").AutoFilter Field:=11, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable > 12"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    MsgBox "Done"




End Sub
 
Upvote 0
Hi Dante,

I am sorry but I am not able to understand what you require from my end.Can you explain me once again. The date is 1st June 2020.
 
Upvote 0
mogss_04 said:
Hi Dante,

I am sorry but I am not able to understand what you require from my end.Can you explain me once again. The date is 1st June 2020.
Click to expand...

Try this

Code: 
Sub populate_values()
    Dim sh As Worksheet, wDate As String, wDat2 As String, lr As Long, lr2 As Long
    Set sh = Sheets("base")
    
    Application.ScreenUpdating = False
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="[SIZE=4][COLOR=#ff0000][B]<[/B][/COLOR][/SIZE]" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date) + 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Blocked Stock"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Blocked"


    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Transit", Operator:=xlOr, Criteria2:="=CC In-Transit"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Transit"


    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Quality Inspection"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Quality Inspection"
    
    '****
    'When " Batch Expiry Date" is blank or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date) - 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
        
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
        
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 1, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 2, Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    '****
    'If Manufacturing date and expiry date both are blanks or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    sh.Range("B1").AutoFilter Field:=11, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable > 12"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    MsgBox "Done"
End Sub
 
Upvote 0
These are the tests you must perform.
Changing the signs.
Help me with that part, please.

Code: 
Sub populate_values()
    Dim sh As Worksheet, wDate As String, wDat2 As String, lr As Long, lr2 As Long
    Set sh = Sheets("base")
    
    Application.ScreenUpdating = False
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:="[SIZE=4][B][COLOR=#ff0000]>[/COLOR][/B][/SIZE]" & wDate, Criteria2:="<" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 12, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), Day(Date) + 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Blocked Stock"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Blocked"




    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Transit", Operator:=xlOr, Criteria2:="=CC In-Transit"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Transit"




    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Quality Inspection"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Quality Inspection"
    
    '****
    'When " Batch Expiry Date" is blank or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date) - 6, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
        
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date), Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
        
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 1, Day(Date)), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 2, Month(Date) + 6, Day(Date)), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    '****
    'If Manufacturing date and expiry date both are blanks or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    sh.Range("B1").AutoFilter Field:=11, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable > 12"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    MsgBox "Done"
End Sub
 
Upvote 0
Hi Dante,

I tried all possible tests on sign changes which you highlighted above on below code

" sh.Range("B1").AutoFilter Field:=12, Criteria1:=">" & wDate, Criteria2:="<" & wDat2 If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
sh.Range("N2:N" & lr).Value = "Usable (7-12)" "

However the 6/1/2020 is still being populated as "Usable (7-12)" instead of "Usable (> 12)".
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
V
  • Question Question
VBA to copy column based on a header matching a cell value (date). The paste formulas into another column. Finally, hard code values after copying.
Replies
2
Views
4K
vekdas
V
cmschmitz24
  • Question Question
Macro Help - Move/Delete based on cell criteria
2
Replies
13
Views
2K
DanteAmor
DanteAmor
D
  • Question Question
Formula or alternative to populate a cell value based on the criteria of multiple other cells in a table.
Replies
2
Views
606
AlanY
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top