CODEWITHSUNDEEP
excelvba
hdk857
hdk857

Reputation: 81

If text contains a value of 2017 or higher set variable equal to a specific value

I need a code where it checks the date and if the date is 2017 or newer the variable B is set to 100 000 otherwise if the date is 2016 or older B is set to 25000. Here is what I have so far but doesn't quite meet my needs. 

   Do Until Y = ""
    'Do until run out of identifier values - reach blank cell in column
    Z = ARng2.Value
    'Z = Identifier Value
    Y = ARng2.Offset(i, 0).Value
    DateString = ARng2.Offset(i, -1)
    If InStr(DateString, "2017") > 0 Then
    B = Sheets("Information Sheet").Range("C7").Value
'100000
    Else
    B = Sheets("Information Sheet").Range("C6").Value
'25000
    End If

Any help would be greatly appreciated. TIA

Upvotes: 0

Views: 31

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Use the Year function against a Date value, and then test it thusly:

If VBA.Year(ARng2.Offset(i, -1).Value2) >= 2017 Then
    B = Sheets("Information Sheet").Range("C7").Value
Else
    B = Sheets("Information Sheet").Range("C6").Value
End If

If ARng2.Offset(i, -1) contains a string literal, you may need to explicitly cast it to a date, like:

If VBA.Year(CDate(ARng2.Offset(i, -1).Value2)) >= 2017 Then...

Upvotes: 1

Related Questions