VBA to Get Maximum Value based on a Date in Another Column

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for your suggestions. If there is an alternative quicker method, by all means, please let me know.

I will explain in simple terms and then expand for my case:

Simple Example:
• I have dates in Column B of sheet “WL.Date” and I am attempting to retrieve corresponding data from sheet “Main” and input it into the cells of Column E.
• Within sheet “Main” Column A has numerical values and Column B has dates.
• For “WL.Date” I loop through Column E and where there is no data, I obtain it from “Main”
• I store the corresponding date for the cell with the missing data (“WL.Main” tab).
• I then go to sheet “Main” and retrieve the maximum value from Column A which corresponds to the date in Column B that was stored from “WL.Date”.


Actual:
• I have a sheet “WL.Date” with a list of dates in the format of “YYYY-MM-DD, DDD” (e.g. 2021-02-03, Wed) in column B.
• I have five columns where the heading of each column corresponds to the sheet name where I want to obtain data for the respective date in each column.
• The heading titles/sheet names, which are in Row 6, are:
Cell E6: : “Main”
Cell F6: “Scalps”
Cell G6: “Backburners”
Cell H6: “Sympathy”
Cell I6: “All”
Within “WL.Main” I store the first heading/sheet name in the string “SheetName” where it starts with “Main” in Column E.
• I loop starting in Column E from the first blank row to the last row.
• I store the corresponding date for the first blank cell and go to “Main” and filter on Column B for that date.
• I then get the maximum value in Column A.
• I repeat the above step for every date where the value in Column E is missing.
• I then repeat for the four remaining columns.
• Below is the code I have written thus far.
• I believe what is happening is that it filters on the date and finds nothing because the date is not being transferred correctly. I get the error: “Run-time error ‘13’: Type mismatch”


VBA Code:
Sub WL_Menu_Stats()

'________________________________________________________________________________________________________
'Turn off alerts, screen updates, and automatic calculation
    'Turn off Display Alerts
        Application.DisplayAlerts = False

    'Turn off Screen Update
        Application.ScreenUpdating = False

    'Turn off Automatic Calculations
        Application.Calculation = xlManual



'________________________________________________________________________________________________________
'Dimensioning
   
    'Dim long
        Dim LastRowStart As Long
        Dim LastRowEnd As Long
        Dim LastRow As Long
       
        Dim i As Long
        Dim j As Long
       
        Dim Count As Long
       
   
    'Dim Strings
        Dim SheetName As String
       
    'Dimensioning Date
        Dim Date_WL As Long
       
    'Dimensioning Range
        Dim RangeMax As Range
       
        Dim Maximum As Double
   
'________________________________________________________________________________________________________
'Code

    'Activate the sheet "WL.Menu"
        Worksheets("WL.Menu").Activate

    'Find the LastRow
        LastRowEnd = Cells(Rows.Count, 3).End(xlUp).Row
       
    'Start a loop to get the values
        For i = 5 To 9
       
        'Find the starting row by finding the last row in the column
                     LastRowStart = Cells(Rows.Count, i).End(xlUp).Row + 1

        'This gets the sheet name where the data needs to be obatined.
            SheetName = Cells(6, i).Value
   
            'This loops through the sheet for the first column (columns E through I)
                For j = LastRowStart To LastRowEnd
                   
                    Date_WL = Range("B" & j).Value 'Stores the data that needs to be looked up
                   
                    Worksheets(SheetName).Activate 'Activate the sheet where the filter needs to be applied _
                        does this step need to be done?
                   
                    Date_WL = Format(CDate(Date_WL), "YYYY-MM-DDD, DDD")
                    MsgBox Date_WL
                   
                    Worksheets(SheetName).Range("A6").AutoFilter Field:=2, Criteria1:= _
                    Format(CDate(Date_WL), "YYYY-MM-DDD, DDD")
                  
                    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
                   
                    Set RangeMax = Worksheets(SheetName).Range("A7:A" & LastRow)
                    Maximum = Application.WorksheetFunction.Max(RangeMax)
                   
                   
                    Worksheets("WL.Menu").Activate
                    Cells(j, i).Value = Maximum
                   
           
                Next j
              
        Next i
   





'________________________________________________________________________________________________________
'XX. Turn on alerts, screen updates, and calculate

        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate


         
 
    End Sub
 
Last edited:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
Worksheets(SheetName).Range("A6").AutoFilter Field:=2, Criteria1:= Date_WL
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Worksheets(SheetName).Range("A6").AutoFilter Field:=2, Criteria1:= Date_WL
Thanks @mart37 for your response, but when I make that change I get the following error

"Run-time error '13': Type Mismatch" on the following line:

VBA Code:
Date_WL = Format(CDate(Date_WL), "YYYY-MM-DDD, DDD")
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
"YYYY-MM-DDD, DDD" ==>"YYYY-MM-DD, DDD"
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
    Dim Date_WL As String
    Date_WL = Format(CDate(Range("B" & j).Value), "YYYY-MM-DD, DDD")
    ActiveSheet.Range("A6").AutoFilter Field:=2, Criteria1:=Date_WL
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,205
Members
416,080
Latest member
blemon

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