Find command 1

[robcarr]

Dear All,

I am still having some problems with the find command, I am looking for the date 12/11, and it can't find it in VBA, but can find it in find in Excel, when I enter the date 11/12, in another column and run the find in VBA again, it finds the date - 11/12, but does not find the one I require 12/11. Below is how I am doing it, I think I must have to set the format of sourcedata to dd/mm, to make sure the data in sourcedata is the same way, I am not sure how to do this, here is the coding I am using.

sourcedate = ActiveSheet.Cells(1, 4)

For n = 1 To Worksheets.Count
Worksheets.Activate
Set myrange = Worksheets.Range("a37:af60&quot
myrange.Find(What:=sourcedate, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(2, 0).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Does anybody have any more ideas on this.

Thanks

 

[xlbo]

sourcedate = format(ActiveSheet.Cells(1, 4),range("A37&quot.numberformat)

should pick up the format from your 1st cell of data that you're looking in
HTH
Rgds
~Geoff~

 

[robcarr]

Geoff,

thanks for that put me the right direction, i now use
sourcedate = Range("d1&quot.value

i think because it was previously using activecell, when I opened a new workbook it changed the value, which was causing the problem, it now finds the date correctly and with out errors.

thanks