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
AJ Mulley
  • 1
  • 1
  • Are the dates in order? Do you need an exact match? Why not just use vlookup() or other excel search/lookup functions? – MatBailie Nov 03 '21 at 00: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! – AJ Mulley Nov 03 '21 at 00:49
  • 1
    Must you use VBA? This sounds like you can use [Match](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match) to get the row number (which should be sufficient since you only have 1 column). – Raymond Wu Nov 03 '21 at 00:56
  • https://stackoverflow.com/q/29203887/62576 – Ken White Nov 03 '21 at 00: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? – AJ Mulley Nov 03 '21 at 01:01
  • I have edited post with the code, apologies BigBen – AJ Mulley Nov 03 '21 at 01:06
  • What is the value in `D10`? And do you have `Dim startingmonth As Date`, `startingmonth = Range("D10").Value` or something similar? What line throws the Overflow error? – BigBen Nov 03 '21 at 01:12
  • Hi BigBen, along the lines of that. I've Edited main post. The date in D10 could be any date of the month. But all the dates in C:C are the first of the month. So I've added a function onto the definition of startingmonth to get the first day of the month of that date. (Therefore when searching in C:C it will find an exact match). Active cell is D12. – AJ Mulley Nov 03 '21 at 01:20
  • `ActiveCell.Offset(-2, 0).Value2` is not `Range("D10").Value2`. What does `Debug.Print ActiveCell.Offset(-2, 0).Value2` return in the Immediate Window? Why are you using `ActiveCell` in the first place? – BigBen Nov 03 '21 at 01:22
  • Hi BigBen, 43440 is the output when I run that line you gave. As the current value in D10 is 6 dec 18. I am using active cell, because I've got a selection change sub on D12, which allows a user to press the cell and will (hopefully) call a macro to find the D10 value amongst C:C range, and return the result's cell address. For now, I think I have found a workaround which I have edited in the main post. I doubt this is very efficient. If you have any guidance I would appreciate it, best – AJ Mulley Nov 03 '21 at 01:35
  • It's still unclear to me why you're using `ActiveCell` at all. If you know that the `startingmonth` is in `D10`. why not use `startingmonth = Range("D10")`? Perhaps also see why [Using `Select` and `ActiveCell` is a bad idea, and how to avoid](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Nov 03 '21 at 01:43

0 Answers0