Find Function in Excel VBA

debugcn Published at Dev

Ameya

I want to search for all Dates belonging to a month. The Month will be selected by the user. I am unable to search all dates, I only get the last date which is entered. I am using find function and unable to provide the specific conditions.

    mois = InputBox("Choisissez le mois (Entrer la valeur Numérique)!!! (1 pour Janvier, 2 pour Fév .... )", "Titre")
   If mois > 0 & mois < 12 Then
    ' Search for matching date
    Set cellsearch = Range("G1:G" & NbrLinesDate).Find(What:=mois_chercher)

    If cellsearch Is Nothing Then
        If mois < 0 Then
        ElseIf mois > 12 Then
        End If
    Else
    ligne = cellsearch.Row
    Date_to_search = Range("G" & ligne).Value
End If
End If
    MsgBox Date_to_search                           '' Checkpoint_1
    JourTest = Day(Date_to_search)
    JourTest = Trim(JourTest)
    MsgBox JourTest                                 '' Checkpoint_2

Mois is the variable, that stores user provided month value. In this code, I am not using this variable mois for searching dates which belongs to this month. I am unable to achieve that.

Date_de_Survenance
 30/01/2013
 31/01/2013
 31/01/2013
 04/02/2013
 05/02/2013
 07/02/2013
 11/02/2013
 13/02/2013
 13/02/2013
 13/02/2013
 15/02/2013
 20/02/2013

Siddharth Rout

See this example. I am not using the Inputbox for taking input but rather using a hard coded value for demonstration purpose.

Lets say your Excel data looks like this.

enter image description here

Simply paste this code in a module and run it.

Code

'
' Excel Constants for Months for Autofilter
'
'   xlFilterAllDatesInPeriodJanuary = 21
'   xlFilterAllDatesInPeriodFebruray = 22
'   xlFilterAllDatesInPeriodMarch = 23
'   xlFilterAllDatesInPeriodApril = 24
'   xlFilterAllDatesInPeriodMay = 25
'   xlFilterAllDatesInPeriodJune = 26
'   xlFilterAllDatesInPeriodJuly = 27
'   xlFilterAllDatesInPeriodAugust = 28
'   xlFilterAllDatesInPeriodSeptember = 29
'   xlFilterAllDatesInPeriodOctober = 30
'   xlFilterAllDatesInPeriodNovember = 31
'   xlFilterAllDatesInPeriodDecember = 32


Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lMnth As Long, constmonth As Long

    '~~> Feb
    lMnth = 2
    constmonth = lMnth + 20 '~~> (See the commented section for constants)

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Remove any filters
        .AutoFilterMode = False

        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:= _
            constmonth, Operator:=xlFilterDynamic
        End With
    End With
End Sub

Output

enter image description here

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-20

Comments

0 comments

From Dev

Related Related

Article

Find Function in Excel VBA

Find Function in Excel VBA

Excel VBA - Find Function - Find a variable

excel vba dir function to find doc not docx

Save .Find() function results to array Excel VBA

VBA Excel: Function to find a string in another workbook

Excel VBA Find Function Taking a Long Time

Where to find a VBA function's source in Excel

Excel VBA .Find Function not finding a date

I use the Find function to find words in vba Excel

Excel-VBA Find Function does not consider first row of column

Excel Function/VBA to find value based on date time and two criteria

using find function and make the resulting cell as selection in excel VBA

Excel VBA Find Function getting runtime Error 1004

Excel vba: .find function returns runtime error 91

Excel Vba .find

Excel VBA .find issue

Excel VBA find #N/A

VBA Excel find and counts

Excel function to VBA

Excel not finding VBA Function

Excel VBA and Group function

Sub or Function in VBA Excel

VBA Excel If statement with AND/OR function

Automate a Excel VBA Function

Excel VBA hlookup function

Excel VBA and Group function

Round function VBA EXCEL

InSTR or find function in VBA

VBA Find Function not working

EXCEL-VBA Return highest value from a find function from a txt file VBA