Ctrl + F can't find value in date format; eomonth formula used

ironny90

Board Regular
Joined
Mar 29, 2022
Messages
72
Office Version
  1. 2010
Platform
  1. Windows
Hi there! I have a column of dates calculated by =eomonth, so it starts with 1/31/2022 and goes on.

Now I am trying to find 3/31/2022, shown as Mar-22 and calculated by =eomonth(A2,1) . The value is 44651 if copy and pasted as value.

The problem is whatever I tried, I could not find it. I tried 03/31/2022,3/31/2022,44651,Mar-22, etc. And changed the search options to value as well.

Does anyone know what's the issue here? Thank you!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
As long as you search for the date using the same format as they are shown in the cells then it should work.
Make sure that it says 'No Format Set' in the second box, if it says anything else then click the dropdown to the right of the format box and clear it.
 
Upvote 0
Hi Jason! Thank you for your quick response! I just posted this. My goal is to use vba to activate the row that cell is in. Can you help with vba as well?

Hi there! I am trying to find a date Mar-22 in an excel sheet. The column starts with 1/31/2022 and goes down with "=eomonth()" formula. Mar-22 is calculated as "=eomonth(A2,1)" and format is date.

I tried to use Ctrl + F to find the date (tried 3/31/2022, 03/31/2022, 44651, etc.), but didn't succeed. I used "=match()" formula to search 44651, the date serial, and succeeded.

Now I am trying to use worksheetfunction.match in vba to find that cell (with 44651), but it didn't find anything.

My goal is to find that cell and activate the row in vba. Can anyone help? Thank you!!!
 
Upvote 0
How do you want the date of interest to be set in vba? Is it based on current date or will you enter the reference date somewhere.

In simple format, you could use something like this with vba to select the date mentioned in this thread
VBA Code:
Range("A:A").Find(CDate("03/31/2022"), , xlValues, xlPart, xlByRows, xlNext, False, False, False).Select
 
Upvote 0
Hi Jason! It's the current month. Next month would be April. column A has the dates as Jan-22, Feb-22, Mar-22, etc. Basically I just want vba to find a specific date and select that row.
Dim row_currentmonth As Long
row_currentmonth = ActiveSheet.Range("A:A").Find(CDate("03/31/2022")).Row.Select

like this?
 
Upvote 0
This will find the end of month date based on the current system date and select the entire row, is that what you need?

I've included an error trap so if the date is not found then nothing will happen rather than the code causing a runtime error.

VBA Code:
Dim rFound As Range
Set rFound = Range("A:A").Find(CDate(Application.WorksheetFunction.EoMonth(Date, 0)), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.EntireRow.Select
 
Upvote 0
Solution
Thank you for your quick response! What's the space between the , , before xlValues?

Can I just copy the code and use it? Do I need to edit anything?
 
Upvote 0
I just ran the code. It didn't find anything. But I know it's there...
 
Upvote 0
NameValueA%B%
1​
54000000​
1%​
2%​
2​
65000000​
1%​
2%​
3​
65000000​
1%​
2%​
4​
65000000​
1%​
2%​
5​
65000000​
1%​
2%​
6​
68000000​
1%​
2%​
7​
65000000​
1%​
2%​
8​
65000000​
1%​
2%​
9​
65000000​
1%​
2%​

I want to start another thread, but since you know so much, I will just paste it here to see if you can help on this as well;)

So I have a summary file with file names in Name column and values and percentages in the next columns.

I want to pull the value and percentages to each file with matching file name (it is named as Review_1_03, Review_2_03, etc.)

The three values are going to the same row (actually it is the row I was asking earlier, Mar-22) but different columns (column numbers are the same in each file, 7, 12 and 17)

Then save file and change the next file.

I kinda of have an idea of using a loop function, but not sure about the details... Let me know if you can help!

Thank you soooooo much!!!
 
Upvote 0
See if this one works, reading your posts again it looks as if you have a custom format of mmm-yy rather than the standard date format that I had based the previous code on.

VBA Code:
Dim rFound As Range
Set rFound = Range("A:B").Find(Format(Application.WorksheetFunction.EoMonth(Date, 0), "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.EntireRow.Select

As for your follow up question, as it's a significant deviation from the original question it would be better if you start a new thread for it.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top