CODEWITHSUNDEEP
vbaexcel
N. Whyter
N. Whyter

Reputation: 123

Determine if cell with date contains a certain year

I am trying to iterate through a column in excel using VBA to determine if any of the cells contain a certain year. I want to keep iterating through to see if it is ever true. The column that I am looking through contains dates in the format dd/mm/yyyy. Below is an example of the column I am iterating through followed by some code that I would like to alter.

C1: (05/01/2013, 06/07/2015, 09/08/2019, ... )

yy = 2018
for i = 1 to 500
    if cells(i,1) contains "yy" then
        cells(i,2) = "yes"
    end if
next i

Thanks in advance for the help.

Upvotes: 1

Views: 1097

Answers (2)

Krystian Kozłowski
Krystian Kozłowski

Reputation: 162

Try this:

Sub FindYear()

yy = 2018
'itarating from first row to last row with value
From i=1 To ActiveWorksheet.Range("A1048576").End(xlUp).Row
If Year(ActiveWorksheet.Range("A" & i).Value = yy Then
    ActiveWorksheet.Range("B" & i).Value = "yes"
End If

End Sub

Upvotes: 0

ERT
ERT

Reputation: 729

Try using the Excel =YEAR() function. Like so:

C1: (05/01/2013, 06/07/2015, 09/08/2019, ... )

yy = 2018
While i <= 500
    if Year(ActiveWorksheet.Cells(i,1)) = yy Then
        ActiveWorksheet.Cells(i,2).Value = "Yes"
    end If
    i = i + 1
Loop

Upvotes: 3

Related Questions