Need macro that Populates usage end date in column T

[anand3dinesh]

Dear All,

Please see below image, i have this job i need to do this manually everyday, if this can be automated it makes my life easier.
I need macro that Populates usage end date in column T
i hope i made instructions clear in Image, if anything more reuired please ask.

Many Thanks for your help.
Cheers

 

[ExcelGzh]

anand3dinesh

If you modify your spreadsheet as follows then there is a formula that will automate the process.

Read and understand all the steps before doing anything

1. Cell B2 enter the date 1/1/19
2. Cell C2 enter this formula =DATE(YEAR(B2),MONTH(B2)+1,1)
3. Copy the formula in Cell C2 across cells D2 to Q2
4. Cell J2 enter the date 1/1/20
5. Custom format cells B2 to Q2 with m. This makes only the month number appear in those cells.
6. Cell B1 enter the formula =B2
7. Custom format cell B1 with yy. This makes only the year appear in cell B1.
8. Copy Cell B1 to cell J1
9. Wherever you have yellow cells enter A, blue cells B and green cells C
10. Wherever you have hatched yellow enter AE, hatched blue BE, hatched green CE
11. Construct 6 conditional formats for the block B3 to Q9 as follows:
11.1 If cell = A then text and fill is yellow
11.2 If cell = B then text and fill is blue
11.3 If cell = C then text and fill is green
11.4 If cell = AE then text and fill is yellow with hatching
11.5 If cell = BE then text and fill is blue with hatching
11.6 If cell = CE then text and fill is green with hatching
11.7 This will make the text invisible against the colored background
11.8 Alternatively you can manually color and shade the cells and then copy them around as the data changes. Either way you must as a minimum have A, B or C in the colored unhatched cells.
12. Put this formula in cell T3
=INDEX($2:$2,1,MAX(IFERROR(LOOKUP(2,1/(B3:Q3="A"),COLUMN(B3:Q3)),0),IFERROR(LOOKUP(2,1/(B3:Q3="B"),COLUMN(B3:Q3)),0),IFERROR(LOOKUP(2,1/(B3:Q3="C"),COLUMN(B3:Q3)),0))+1)
More information the workings of the Lookup part of the formula can be found by entering excel find last non-blank cell into a search engine
13. Custom format cell T3 with m"_"yy
14. Copy the formula in T3 into cells T4 to T9
15. The results for T8 and T9 will be wrong unless you construct a third date block for 2021 or modify the formula so that if the result of the Max function is 17 (column Q) then the formula result is 1_21.
16. If you add a 2021 block, make sure you modify the formula to include that block

 

[anand3dinesh]

anand3dinesh

If you modify your spreadsheet as follows then there is a formula that will automate the process.

Read and understand all the steps before doing anything

1. Cell B2 enter the date 1/1/19
2. Cell C2 enter this formula =DATE(YEAR(B2),MONTH(B2)+1,1)
3. Copy the formula in Cell C2 across cells D2 to Q2
4. Cell J2 enter the date 1/1/20
5. Custom format cells B2 to Q2 with m. This makes only the month number appear in those cells.
6. Cell B1 enter the formula =B2
7. Custom format cell B1 with yy. This makes only the year appear in cell B1.
8. Copy Cell B1 to cell J1
9. Wherever you have yellow cells enter A, blue cells B and green cells C
10. Wherever you have hatched yellow enter AE, hatched blue BE, hatched green CE
11. Construct 6 conditional formats for the block B3 to Q9 as follows:
11.1 If cell = A then text and fill is yellow
11.2 If cell = B then text and fill is blue
11.3 If cell = C then text and fill is green
11.4 If cell = AE then text and fill is yellow with hatching
11.5 If cell = BE then text and fill is blue with hatching
11.6 If cell = CE then text and fill is green with hatching
11.7 This will make the text invisible against the colored background
11.8 Alternatively you can manually color and shade the cells and then copy them around as the data changes. Either way you must as a minimum have A, B or C in the colored unhatched cells.
12. Put this formula in cell T3
=INDEX($2:$2,1,MAX(IFERROR(LOOKUP(2,1/(B3:Q3="A"),COLUMN(B3:Q3)),0),IFERROR(LOOKUP(2,1/(B3:Q3="B"),COLUMN(B3:Q3)),0),IFERROR(LOOKUP(2,1/(B3:Q3="C"),COLUMN(B3:Q3)),0))+1)
More information the workings of the Lookup part of the formula can be found by entering excel find last non-blank cell into a search engine
13. Custom format cell T3 with m"_"yy
14. Copy the formula in T3 into cells T4 to T9
15. The results for T8 and T9 will be wrong unless you construct a third date block for 2021 or modify the formula so that if the result of the Max function is 17 (column Q) then the formula result is 1_21.
16. If you add a 2021 block, make sure you modify the formula to include that block

Hi I really appriciate your time and work you done for me.
but sorry i connot modify my Master documnet like that. all i need a macro that gives me user end date without changing my master documnt

 

[ExcelGzh]

Just out of curiosity, why does the solution specifically have to be a macro?

 

[anand3dinesh]

Just out of curiosity, why does the solution specifically have to be a macro?

because i cannot modify my Master Tracker, Also i am learning VBA if you provide me a macro, it will be helpful for me to learn new ideas

 

[Akuini]

Hi, @anand3dinesh
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.

 

[anand3dinesh]

Hi, @anand3dinesh
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.

Hi Please see Google Drive link below

VBA Query.xlsx

drive.google.com

 

[Akuini]

I downloaded your file.
You mark the extension with a style not black/color. Sorry, actually I don't know how to refer a style in code.
I suggest you change that style to a letter say "x" so it will be easy to "find" in code.
Or maybe someone else here can help you.

 

[anand3dinesh]

I downloaded your file.
You mark the extension with a style not black/color. Sorry, actually I don't know how to refer a style in code.
I suggest you change that style to a letter say "x" so it will be easy to "find" in code.
Or maybe someone else here can help you.

Thanks for your try, sorry i cannot modify anything in my tracker.

 

[ExcelGzh]

anand3dinesh

This VBA code will do what you want under the following conditions:
1. The code is in the tracker workbook. If it is in a different workbook then some additional code will be required to identify the tracker workbook.
2. The layout of the sheet is exactly as is shown in your sample. If any rows or columns are different it won't work.

Sub PopulateColumnT()
  Dim ColorA As Long
  Dim ColorB As Long
  Dim ColorC As Long
  Dim StyleHatch As Long
  Dim ColumnNum As Long
  Dim RowNum As Long
  Dim YearNum As Integer
  
  ColorA = Range("U2").Interior.Color
  ColorB = Range("U3").Interior.Color
  ColorC = Range("U4").Interior.Color
  StyleHatch = Range("U5").Interior.Pattern
  
  For RowNum = 3 To Cells(2, 1).End(xlDown).Row
    For ColumnNum = Cells(2, 1).End(xlToRight).Column To 2 Step -1
      If (Cells(RowNum, ColumnNum).Interior.Color = ColorA Or Cells(RowNum, ColumnNum).Interior.Color = ColorB Or Cells(RowNum, ColumnNum).Interior.Color = ColorC) And Cells(RowNum, ColumnNum).Interior.Pattern <> StyleHatch Then
        Select Case Len(Cells(2, ColumnNum + 1))
          Case 0
            Cells(RowNum, 20) = "1_21"
          Case Else
            If Cells(2, ColumnNum) = 1 Then
              YearNum = Cells(1, ColumnNum)
            Else
              YearNum = Cells(1, Cells(1, ColumnNum).End(xlToLeft).Column)
            End If
            Cells(RowNum, 20) = Cells(2, ColumnNum + 1) & "_" & YearNum - 2000
        End Select
        Exit For
      End If
    Next ColumnNum
  Next RowNum
  
End Sub