Find Date Month

charlesdh · #1 07-19-2018, 06:13 PM

Hi,

Another brain problem.
I have a cell formate for Dates.
The cell shows "May 31 2018" if you look at the formula bar it shows 5/31/2018.
In vba I'm trying to to do a find just for "May". But, having issues.
Can someone let this old brain give a suggestion on this.

Thanks

macropod · #2 07-19-2018, 06:48 PM

Code:

Dim Rng As Range
Set Rng = ActiveSheet.Cells.Find(What:="May", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
MsgBox Rng.Address

charlesdh · #3 07-21-2018, 12:35 PM

macropod,
Thanks for the reply. However, I missed pointing out that I'm using a string variable to get a date from another worksheet and then go to another worksheet and find the "Month" which is in row 2. The dates in this row are formate as "mmm-yyy". In the formula bar it will show 5/1/2018. And the row/column for this date shows "May-2018". I have a code:
"strdate = Format(strdate, "mmm" which shows "May". When I try to find "MAY" it does not find it.
I could reformat the Month row to text which I believe would work.

Thanks

macropod · #4 07-21-2018, 11:39 PM

You could also use:
strdate = Split(strdate, "-")(0)

I'd also have thought it fairly obvious that you could change:
MatchCase:=True
to:
MatchCase:=False

ArviLaanemets · #5 07-22-2018, 01:05 AM

Quote:

Originally Posted by charlesdh

The cell shows "May 31 2018" if you look at the formula bar it shows 5/31/2018.

This indicates, that in cell is date, not date string. Format the cell as general for moment - you see a long integer value in cell. This is the real value stored there.

Quote:

Originally Posted by charlesdh

I'm using a string variable to get a date from another worksheet and then go to another worksheet and find the "Month" which is in row 2. The dates in this row are formate as "mmm-yyy". In the formula bar it will show 5/1/2018. And the row/column for this date shows "May-2018"

So on second sheet are also dates really?

Read Date from one sheet into variable as

Code:

YourVariable = FORMAT(YourDateOnSheet1,"yyyy-mm"))

On 2nd sheet, search for cell(s) where

Code:

FORMAT(YourDateOnSheet2,"yyyy-mm"))= YourVariable

macropod · #6 07-22-2018, 04:41 AM

Quote:

Originally Posted by ArviLaanemets

This indicates, that in cell is date, not date string.

Fairly basic and, I think well-enough understood in this case. Charles' issue was with finding the month via its formatting, using VBA. The code I provided already does that.

ArviLaanemets · #7 07-22-2018, 05:54 AM

Quote:

Originally Posted by macropod

Charles' issue was with finding the month via its formatting, using VBA.

True! But unnecessarily complicated! Really his task was comparing dates on 2 sheets:
Was there a 1st of month in other sheet from same month as month of checked date?
Or (which option is right one remains unclear) was there a any date in other sheet with month same as month of checked date?

Using formats for it was only his way to do it! As result he got an issue :-)

charlesdh · #8 07-23-2018, 11:29 AM

Hi,
Still can not get this right. I attached a sample workbook.
I have data in sheet2 that has a "Batch Run Date" that I need to find in sheet1.

The code I have does not work.
If you could take a look at the file.

Thanks

charlesdh · #9 07-23-2018, 11:40 AM

Hi,

As usual I found a solution as to my problem after a request from you. Would still like see what I did wrong with the code that I have in the file.

Charles

macropod · #10 07-23-2018, 04:21 PM

Quote:

Originally Posted by charlesdh

Would still like see what I did wrong with the code that I have in the file

Well, you said you were looking for just the month, but strdate is only ever defined as a date in the format "mmm-yyyy". Naturally, that looks for the month and year, and will only find them if separated by a '-'. Furthermore, your Find expression looks at the formulae, not the displayed strings and requires a whole-of-cell match instead of a partial match. Try using something along the lines of:

Code:

Sub FindDate()
Dim strdate As String, rCell As Range, lReply As Long
strdate = Format(Sheets("Sheet2").Cells(2, 4).Text, "mmm")
Set rCell = Sheets("Sheet1").Cells.Find(What:=strdate, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rCell Is Nothing Then MsgBox rCell.Address
End Sub

p45cal · #11 07-24-2018, 05:02 AM

I suspect that you'll be doing this not for just one cell (Sheets("Sheet2").Cells(2, 4)), but for many, perhaps cells(D2:Dn)?
If this is the case, then it might be an idea to set up a lookup table once, to be used over and over again, to determine the column you want to place data in:

Code:

Sub blah()
 'Set up a lookup table once only from the top row of "Sheet1":
mylookup = Sheets("Sheet1").Range("A1:J1").Value
For i = 1 To UBound(mylookup, 2)
  If IsDate(mylookup(1, i)) Then mylookup(1, i) = Format(mylookup(1, i), "yyyy-m")
Next i
  
'then presumably below here you're iterating through multiple cells, eg.:
For Each cll In Sheets("Sheet2").Range("D2:D5").Cells
  colum = Application.Match(Format(cll.Value, "yyyy-m"), mylookup, 0)     'colum is the column number on "Sheet1", and can be used:
  If Not IsError(colum) Then Application.Goto Sheets("Sheet1").Cells(1, colum)
Next cll
End Sub

But…
it looks as though you're building a new table from another, in which case it might be faster/easier/more robust to do this with a pivot table.
If you supply a file with more data and a sheet with the kind of results you're looking for, we should be able to show you how.

charlesdh · #12 07-24-2018, 06:11 PM

Arv, macropod and p45cal

Thanks for the great help. I'm grateful for all of the help I get from this forum.
p45cal I'll talk to the person I'm trying to help. And, yes I building a new table from another one.

Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
if cell contains date, then month, else blank	Merlot	Excel	4	10-03-2016 03:27 PM
Auto update excel graph range, ignore data when date reads 00-Month-00	SavGDK	Excel	2	06-24-2016 08:05 AM
Excel day date month year Function	greginky	Excel	1	01-05-2016 02:19 AM
Filter by a Month that falls within a date range	MattG1225	Excel	2	12-03-2015 07:44 AM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM