Freddie has attended:
Excel VBA Introduction course
Search for prior month date in a range
Hi Guys
I'm trying to establish whether or not excel can find a prior month's date in a spreadsheet. What I have is the below;
FileYear = Year(Date)
strFileMonth = Format(DateAdd("m", -1, Now), "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
FilePath = "L:\ClientInterest\CI shared\~ Month End Interest Process ~\Monthly Rates\" & FileYear & "\" & strFileMonth & "\*FX Rates*.xlsx"
RateMonth = Format(DateAdd("m", -1, Now), "dd.mm.yyyy")
Dim strFindWhat As String
strFindWhat = RateMonth
Windows("KPI Interest 2015 - FULL FILE (CLIENT INTEREST)_DUMMY.xlsx").Activate
Sheets("Rates").Select
Rows("1:1").Select
Search("" * strFindWhat * "", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
However it keeps falling over at the search part. Can you help me resolve?
Tks!
Freddie
RE: Search for prior month date in a range
Hi Freddie,
Thank you for the forum question.
Use a for each loop to loop through the selection.
Dim cell As Range
For Each cell In Selection
If cell Like "*strFindWhat*" Then
cell.Select
End If
Next cell
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
