CODEWITHSUNDEEP
vbaexcel
Loudo
Loudo

Reputation: 69

VBA Excel - Define range by yesterdays date

Within VBA, I am trying to set the range as the day previous to when the workbook is open (e.g. Workbook is open on the 13/04/2017, I want the range to be 12/04/2017)

I would like VBA to search Column C for the previous day and when it finds it, it then selects the cells adjacent to it (A:I preferably).

Then, with this information, I would like the range to be set as to what was found with the search - This will then be converted to HTML and sent as an email.

Hopefully what I'm requesting makes sense.

Upvotes: 2

Views: 10330

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

Try the code below (explanation inside the code comments):

Option Explicit

Sub FindYesterdaysDateRow()

Dim Rng     As Range
Dim YesterD As Date

YesterD = DateAdd("d", -1, Date) ' <-- get yesterday's date

' the following line will work if you have column C formatted as "Date"
Set Rng = Range("C:C").Find(What:=YesterD, LookIn:=xlValues, LookAt:=xlWhole)

If Not Rng Is Nothing Then '<-- Find was successful
    Range("A" & Rng.Row & ":I" & Rng.Row).Select
Else ' <-- Find was unable to find yesterday's date
    MsgBox "Yesterday's date not found in Column C"
End If


End Sub

Upvotes: 0

sadtank
sadtank

Reputation: 340

I'll get you 90% there. The code you want should look something like this. Based on your question I don't fully understand what vars you wanted set.

Dim d As Date
 d = DateAdd("d", -1, Date)

Dim x As Range
Dim a As String

'Range("e1").Value = d

Columns("C:C").Select
a = Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Address

'Range(a).Offset(0, 31).Range("A1:B1").Select
'adjust this
' x = Range(a).Offset(0, 31).Range("A1:B1").Address

Upvotes: 1

Related Questions