Need macro that Populates usage end date in column T

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Query.JPG
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Just out of curiosity, why does the solution specifically have to be a macro?
 

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,425
Messages
5,642,051
Members
417,251
Latest member
Dordrecht

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
Top