#1
|
|||
|
|||
Find Date Month
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 |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Quote:
Quote:
Read Date from one sheet into variable as Code:
YourVariable = FORMAT(YourDateOnSheet1,"yyyy-mm")) Code:
FORMAT(YourDateOnSheet2,"yyyy-mm"))= YourVariable |
#6
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Quote:
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 :-) |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
||||
|
||||
Quote:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
||||
|
||||
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 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. |
#12
|
|||
|
|||
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 |