Extract the day of date from a worksheet range-CodePudding

I am working on a monthly reporting macro which would require me to extract the day number and month number from a range and get the maximum day number ( E.g 1,5,7,9) to proceed to the next step. I have a code that doesn't seem like an ideal solution.

Say for example, I am extracting the day from Range A1:A4, although when the real codes are written it will be from A1 to the last row. So the values from Range A1 to A4 will be as follows

A1= 12/10/2022 A2= 14/6/2020 A3=3/3/2020 A4= 1/4/2021

Do note that the date conventions are in British Convention (dd/mm/yyyy) on the Excel Range. Similarly I would require to extract the month as well, but if I can get a code for days then it wouldn't be a problem to get for Month as well.*

Sub TestR1()
    Dim MonthArray(1 To 4) As Variant
    Dim x As Double
    Dim i As Byte
    Dim Rng As Range
    Dim ExDate As Date
    Dim Mx As Long
   
    Set Rng = Range("A1:A4")
             
         
    For i = 1 To 4
    Set Rng = Range("A" & i)
    ExDate = VBA.CDate(Range("A" & i).Value)
    x = VBA.Day(ExDate)
           
    MonthArray(i) = x
    Next i
   
    Mx = Excel.WorksheetFunction.Max(MonthArray)
   
    End Sub

CodePudding user response：

This code and your explanation is very confusing. I think you're trying to get an array of months and days for each row but your code is:

Reading the date from a single cell, not four on the row.
Extracting the day part of that single date, and;
Plugging that day value into an array called MonthArray.

If I understand you correctly, I'd do something like this:

Sub ParseDates(src As Range)
    Dim dates() As Variant
    dates = src.Value

    Dim rCount As Long
    rCount = src.Rows.Count

    Dim cCount As Long
    cCount = src.Columns.Count

    Dim days() As Variant
    Dim months() As Variant
    Dim dt As Date

    Dim r As Long, c As Long
    For r = 1 To rCount
        For c = 1 To cCount
            dt = CDate(dates(r, c))
            days(r, c) = Day(dt)
            months(r, c) = Month(dt)
        Next c
    Next r

    ... ' Whatever you want to do with your values.
End Sub

CodePudding user response：

Extract Days and Months

Option Explicit

Sub TestR1()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim rg As Range
    Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    Dim rCount As Long: rCount = rg.Rows.Count
    
    Dim Data()
    If rCount = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    Dim Months() As Long: ReDim Months(1 To rCount)
    Dim Days() As Long: ReDim Days(1 To rCount)
    
    Dim r As Long
    
    For r = 1 To rCount
        Days(r) = Day(Data(r, 1))
        Months(r) = Month(Data(r, 1))
    Next r
    
    Debug.Print "Index", "Day", "Month"
    
    For r = 1 To rCount
        Debug.Print r, Days(r), Months(r)
    Next r
    
    Debug.Print "Max Day", "Max Month"
    Debug.Print Application.Max(Days), Application.Max(Months)

End Sub

Results in the Immediate Window (Ctrl G)

Index         Day           Month
 1             20            4 
 2             16            2 
 3             27            2 
 4             15            3 
 5             26            1 
 6             4             12 
 7             3             2 
 8             26            1 
 9             20            4 
Max Day       Max Month
 27            12