Identifying a variety of holidays in a data set

scttsmn · Jun 10th 2016

While searching the internet, I came across the following code, which searches a data set for the macro-defined search terms, and copies them into a separate workbook. This code works splendidly, however, I would like to modify it to search for certain holidays. However, I have never worked with these types of date-defining/constricting codes before. In a simple example, how might I modify this code to search for January 1, December 24, and December 25... instead of Fruit 2, Fruit 5, and Fruit 18?

Thank you in advance for your help!

Code

Sub FruitBasket()
 
Dim rngCell As Range
Dim lngLstRow As Long
Dim strFruit() As String
Dim intFruitMax As Integer
 
intFruitMax = 3
ReDim strFruit(1 To intFruitMax)
 
strFruit(1) = "Fruit 2"
strFruit(2) = "Fruit 5"
strFruit(3) = "Fruit 18"
 
lngLstRow = ActiveSheet.UsedRange.Rows.Count
 
For Each rngCell In Range("A2:A" & lngLstRow)
    For i = 1 To intFruitMax
        If strFruit(i) = rngCell.Value Then
            rngCell.EntireRow.Copy
                Sheets("Inventory").Select
                    Range("A65536").End(xlUp).Offset(1, 0).Select
                    Selection.PasteSpecial xlPasteValues
                Sheets("Fruit").Select
        End If
    Next i
Next
 
End Sub

Display More

**S O** · Jun 10th 2016

Re: Identifying a variety of holidays in a data set

If you just want to modify the existing code, then change the data type:

Code

Sub FruitBasket() 
     
    Dim rngCell As Range 
    Dim lngLstRow As Long 
    Dim searchDates() As Date    '// modified
    Dim intFruitMax As Integer 
     
    intFruitMax = 3 
    Redim strFruit(1 To intFruitMax) 
     
    strFruit(1) = CDate("01/01/2016") '// modified
    strFruit(2) = CDate("24/12/2015") '// modified
    strFruit(3) = CDate("25/12/2015") '// modified
     
    lngLstRow = ActiveSheet.UsedRange.Rows.Count 
     
    For Each rngCell In Range("A2:A" & lngLstRow) 
        For i = 1 To intFruitMax 
            If strFruit(i) = rngCell.Value Then 
                rngCell.EntireRow.Copy 
                Sheets("Inventory").Select 
                Range("A65536").End(xlUp).Offset(1, 0).Select 
                Selection.PasteSpecial xlPasteValues 
                Sheets("Fruit").Select 
            End If 
        Next i 
    Next 
     
End Sub

Display More

Otherwise there are better ways to do this kind of thing.

skywriter · Jun 10th 2016

Re: Identifying a variety of holidays in a data set

I don't think I would loop I would just filter the data.

It would be best if you posted a workbook.

[sw]*[/sw]

scttsmn · Jun 11th 2016

Re: Identifying a variety of holidays in a data set

Thank you for your quick response!

This is certainly one way of accomplishing the task, however, I would rather not hard code absolute dates into the macro if I don't have to. I would like to set up the macro to identify "December 25" in ANY year (not just 2015). Could this existing code be modified to achieve this task? The more advanced application would be that I would use the macro to identify a variety of dates (in any year).

Thank you again!

scttsmn · Jun 13th 2016

Re: Identifying a variety of holidays in a data set

I found a solution that will work for me - I did not use the code from my original post. Below is an excerpt of the coding from my Macro (granted, all the variables are not defined in this excerpt). I embedded an IsDate function in an IF statement, to verify that the cell value was a date - if the value is not a date, then the "LSearchRowMod = LSearchRowMod + 1" portion moves the search to the next line. If a date is found and it matches the "month" and "day" criteria, then it is copied into Sheet2. I can then replicate this code for each date that I want to search.

Thank you again for your help.

Code

srchDate = CDate("12/24/2000")


For Each rngCell In Range(SrchClmn & LSearchRowMod & ":" & SrchClmn & lngLstRow)
           
            If IsDate(Range(SrchClmn & CStr(LSearchRowMod))) Then
            
                If Month(Range(SrchClmn & CStr(LSearchRowMod)).Value) = Month(srchDate) And Day(Range(SrchClmn & CStr(LSearchRowMod)).Value) = Day(srchDate) Then
                    
                    'Select row in Sheet1 to copy
                    Rows(CStr(LSearchRowMod) & ":" & CStr(LSearchRowMod)).Select
                    Selection.Copy
                    
                    'Paste row into Sheet2 in next row
                    Sheets("Sheet2").Select
                    Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                    ActiveSheet.Paste
                    
                    'Move counter to next row
                    LCopyToRow = LCopyToRow + 1
                    
                    'Go back to Sheet1 to continue searching
                    Sheets(shtName).Select
                    
                End If
                
                LSearchRowMod = LSearchRowMod + 1
                
            Else
            LSearchRowMod = LSearchRowMod + 1
            End If
            
        Next

Display More

Identifying a variety of holidays in a data set

Participate now!

Share