0

I'm extremely new to VBA. I'm trying to get Excel to return the cell address, after searching for a date in a range of dates.

I have a range of dates in Column C all set as true date format.

1/12/2018 1/1/2019 1/2/2019 ...

And in D10 a single date. I want some VBA code to search Column C using the date in D10 as its criteria, and return the cell address of the result.

Thanks in advance for any help A


Edit.

Earlier in the vba I have defined D10 as a date variable called startingmonth.

This is the code I have. I was just using 43435 as text as a test, I want to use startingmonth here (which is D10) but when I replace 43435 with the variable, I receive error 6 overflow.

Dim startingmonth As Date
startingmonth = Excel.Application.WorksheetFunction.EoMonth(ActiveCell.Offset(-2, 0).Value2, -1) + 1

Dim rgFound As Range
Set rgFound = Range("C:C").Find("43435")
MsgBox rgFound.Address

Current workaround (from active cell D12)

ActiveCell.Offset(1, -1).Select

If ActiveCell.Value = startingmonth Then
MsgBox ActiveCell.Address
Else
GoTo Searchloop
End If

Searchloop:

ActiveCell.Offset(1, 0).Select

If ActiveCell.Value = startingmonth Then
MsgBox ActiveCell.Address
Else
GoTo Searchloop
End If
11
  • Are the dates in order? Do you need an exact match? Why not just use vlookup() or other excel search/lookup functions? Commented Nov 3, 2021 at 0:49
  • Hi, sorry I'm new here. Here you go: Dim rgFound As Range Set rgFound = Range("C:C").Find("43435") MsgBox rgFound.Address This is what I have found - and it works if the dates in C:C are as text, but fails when values in C:C are as date type Yes, dates are in order.. I guess I'm not knowledgable enough at the moment to use vlookup via vba.. very new! Commented Nov 3, 2021 at 0:49
  • 1
    Must you use VBA? This sounds like you can use Match to get the row number (which should be sufficient since you only have 1 column). Commented Nov 3, 2021 at 0:56
  • stackoverflow.com/q/29203887/62576 Commented Nov 3, 2021 at 0:56
  • I have D10 defined as a date variable called startingmonth. I was trying to replace 43435 with startingmonth, but it doesn't like that. I guess it's a date format thing? Commented Nov 3, 2021 at 1:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.