locate the cell address of dates formated with mmm-yy

[anilsharaf]

In sheet ePay Column A there are month Names of the Finencial Year. Mar-21 to Jan-22. I tried several code snippests but my code fails in the loop. Window7, Excel 2007, Date system Date Formate is United Kingdom short date dd/mm/yy Long date formate is dd-mmmm-yyyy. I used the codes: when I do this in User Interface Find all works. The sheet is a downloaded sheet from ePay Database. Actually I have to retrive data from many sheets. I have done this locating the DDOCode 62014 in Column A (A25). But some times it is not available. But the Pay of Month is always there. So I want to retrive the data locating cell in which Pay of Months are entered.
I would be greatful for your help. Thanks in advance.
The Code is:

Sub XX_Failed_XXDateBasedSearchWriteAmountToSheet_MyCopiedData()

NextEmployeeRow = 11

LastRow = ActiveSheet.UsedRange.Rows.Count - 4



1 'read the number of employees from sheet finding "Dates in mmm-yy Format"

With ActiveSheet.Range("A" & NextEmployeeRow & ":A" & LastRow)



'Search is based on Date Formate of Sheet

Application.FindFormat.Clear

Application.FindFormat.NumberFormat = "mmm-yy"

Set cell = .Find(What:="", LookIn:=xlFormulas, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=True)

x = cell

If Not cell Is Nothing Then

firstAddress = cell.Address

NextEmployeeRow = Range(cell.Address).Row + 1

Do

TotalEntries = TotalEntries + 1

EmId = Range(cell.Address).Offset(-2, 1)

EmNm = Range(cell.Address).Offset(2, 1)

'Range(cell.Address).Offset(-3, 1).Resize(7, 13).Select

'cell.Value = 5

Set cell = .FindNext

x = cell

Loop While Not cell Is Nothing And cell.Address <> firstAddress

End If

End With

end sub

The Mini Sheet is:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
11		Sr.No.	Basic	StagnationInc	Trans.Allow.	WashingAllow	Sp.PoliceAllow	Conn.Ch.Allow	WaterAllow	GrossSalary	GPF/DPF/Tier1	GrainRec	GIS	TotalDedn.
12		EmployeeID	D.Pay	InterimRelief	Depu.Allow	CashierAllow	BigularAllow	SpecialAllow	NewsPrAllow		G/DPF/CPS.Rc	ComputerRec	PLI	NetSalary
13		EmployeeCode	GradePay	OtherPay	TribalAllow	OtherAllow	MedalAllow	RobeAllow	Mag.Allow		HB.Rec	PayRec	HouseRent
14	Pay Month Year	PF/PRAN NO.	NPA	D.A.	HRA	Wages	ArmourAllow	SumptAllow	Elec.Allow		CarRec	OtherRec	WaterCharge
15	Salary Taken DDO	BankA/C	SpecialPay	AdditionalDA	ProjectAllow	FixTA	DIAllow	LibAllow	Sec.Allow		CycleRec	IncomeTax	Mot.Vec.Char
16		Name	PersonalPay	Fee	TrainingAllow	P.AaharAllow	UniformAllow	TeleAllow	-		MotorCycleRec	Prof.Tax	OtherDed
17			Honorarium	CCA	MedicalAllow	Sp.RationAllow	MT.Allow	H.O.Allow	-		FestivalRec	FBF	Ex.PayRec
18
19
20	Designation :Regular Peon, No. of Post :2, Wide Sanction Order No. and Date :F/44/Fin/,-
21		1	23600	0	0	50	0	0	0	27310	2643	0	180	2823
22		1	0	0	0	0	0	0	0		0	0	0	24487
23		6200140030	0	0	0	0	0	0	0		0	0	0
24	Mar-21	1.1001E+11	0	2832	628	0	0	0	0		0	0	0
25	620014	11502540831	0	0	0	0	0	0	0		0	0	0
26		Marina	0	0	0	0	0	0	0		0	0	0
27			0	0	200	0	0	0	0		0	0	0
28
29
30		2	23600	0	0	50	0	0	0	27310	2643	0	180	3610
31		1	0	0	0	0	0	0	0		0	0	0	23700
32		6200140030	0	0	0	0	0	0	0		0	0	0
33	Apr-21	1.1001E+11	0	2832	628	0	0	0	0		0	0	0
34	620014	11502540831	0	0	0	0	0	0	0		0	0	0
35		Marina	0	0	0	0	0	0	0		0	0	787
36			0	0	200	0	0	0	0		0	0	0
37
38
39		3	23600	0	0	50	0	0	0	27310	2643	0	180	2823
40		1	0	0	0	0	0	0	0		0	0	0	24487
41		6200140030	0	0	0	0	0	0	0		0	0	0
42	May-21	1.1001E+11	0	2832	628	0	0	0	0		0	0	0
43	620014	11502540831	0	0	0	0	0	0	0		0	0	0
44		Marina	0	0	0	0	0	0	0		0	0	0
45			0	0	200	0	0	0	0		0	0	0
46
47
48		4	23600	0	0	50	0	0	0	27310	2643	0	180	2823
49		1	0	0	0	0	0	0	0		0	0	0	24487
50		6200140030	0	0	0	0	0	0	0		0	0	0
51	Jun-21	1.1001E+11	0	2832	628	0	0	0	0		0	0	0
52	620014	11502540831	0	0	0	0	0	0	0		0	0	0
53		Marina	0	0	0	0	0	0	0		0	0	0
54			0	0	200	0	0	0	0		0	0	0
ePay1

 

[Micron]

Your posted code doesn't compile (there's an issue with the line continuation characters). Since I use Option Explicit at the top of every module, I had to declare about 8 missing variables. It will run, but starting at A11, your code (Set cell = .Find(What:="",) does not find "" so cell is Nothing thus X = cell also fails.
Maybe you could just search the range for anything that is a date data type, like
If IsDate (CDate(Range("A23"))) Then (where the range is determined in your loop).
I think you'll find that if you put that in the vb editor immediate window as

?IsDate (CDate(Range("A23")))

"True" will be returned because the unformatted value in that cell is 44257, which is a valid date value.

 

[BSALV]

2 options
- look in column A and retrieve all blocks of cells with constants (=not empty, no formula) & that block starts > row 10 and the block is 2 cells
- look for a certain date in column A

Sub MijnKolomA()

     With ActiveSheet
          Set c = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)     'usedrange of column A
          Set c1 = c.SpecialCells(xlConstants)

     'option1
          For Each ar In c1.Areas                               'loop through all the block of constants in column A
               If ar.Cells(1).Row > 10 And ar.Rows.Count = 2 Then     'startrow>10 and a block of 2 rows
                    MsgBox ar.Address & vbLf & Format(ar.Cells(1), "dd mm yyyy") & vbLf & ar.Cells(2)
               End If
          Next

     'option2
          a = c.Value2                                          'column A -> array   !!!! value2 !!!!!
          mydate = DateSerial(2021, 5, 1)                       ' a date
          r = Application.Match(CDbl(mydate), a, 0)
          If IsNumeric(r) Then MsgBox "your date " & Format(mydate, "dd-mm-yy") & " is in cell " & c.Cells(r, 1).Address
     End With

End Sub

 

[Micron]

Just discovered that it didn't find "" for me because I didn't bother to change the date values from numbers to values formatted as mmm-yy and you had set the Find SearchFormat parameter to True. Not sure why you're looking at formulas instead of values though. I suspect you get stuck in your loop as it doesn't seem to increment anything, which must be what you meant by

I tried several code snippests but my code fails in the loop.

Sorry, I should bow out except to say that you might be able to use IsDate as suggested.

 

[anilsharaf]

Thanks both to respected Micron and BSALV, I am going to use the code suggested by both of you. But I just found a trick and visited the Forum to tell about that. The trick is using wild card in Find VBA. This trick does not work in Excel Interface. It works only with VBA. The trick is to find all dates formated with "mmm-yy" using the wild card. Thanks to Paul Kelly he has given a full Detail of "Find VBA" at Excel VBA Find - A Complete Guide - Excel Macro Mastery . Using (Topic 15) the wild card string "*-2?" finds all dates that I needed to search for. This The code is below. I will try codes given by both of You. Because it may give me some new idea and some new dimension. Thanks again.
Code:

Sub FindDate_Formated_mmm_yy()
With ActiveSheet.Range("A:A")
.Find("*-2?", LookIn:=xlValues).Activate
Do
.FindNext(ActiveCell).Activate
Loop
End With
 End Sub

 

[Rick Rothstein]

This trick does not work in Excel Interface. It works only with VBA.

Sure it works in the Excel Interface. Select your column, press CTRL+F to bring up the Find dialog box, type *-2? in the "Find what" field, click the "Options>>" button and make sure the "Look in" drop-down is set to "Values" and that the "Match entire cell contents" check box is checked, then click the "Find All" button and follow that by pressing CTRL+A. You can now close the dialog box and the cells with the format you wanted will all be selected.

 

[BSALV]

then click the "Find All" button and follow that by pressing CTRL+A.

That's an interesting thought and i tried to incorporate that in VBA.
So i started with the macro-recorder, but that one didn't record anything of that "find"-session.
Is there a possibility to simulate all this with "Sendkey" or something similar to get at once all those cells instead of in a loop "findnext"

 

[Rick Rothstein]

I have never found a way to replicate "Find All"... whoever designed the Excel engine within VBA left that functionality out. I am not sure if SendKeys can be made to work or not.

 

[BSALV]

thanks

 

[anilsharaf]

Thanks to Rick, Yes it also worked in Excel Interface.