Problem with finding dates in Excel Macro

[Ped_Esc]

Hi


Im having problems with this macro (above), only works until date 09/07/2004, from 10 to 31 doesn't work. Please Help me!!

Sub Macro1()
Workbooks.Open Filename:="C:\TEMP\faxdata.DBF"
'the structure of that table is like this: fdata (date),hampm (char2, values AM and PM only), then 10 fields with values all integer'
Windows("C2004.xls").Activate
Dim rng As Range
Set rng = Sheets("07").Range("B7:B80")
'at B7 begins my dates from 01/07/04 until 31/07/04
For Each Cell In rng
If Cell <> "" Then 'explanation above at (1) mark
Data = Cell ' here I keep my Date
OPE = IIf(Cell.Offset(0, 1).Value = "a", "AM", "PM")
'I change a/p to AM/PM 'cause in faxdata the value is like that
Windows("faxdata.dbf").Activate
With Worksheets("faxdata").Range("A1:A1000")
Set c = .Find(Data, LookIn:=xlValues) 'I found this example and I take it
'but Idk how exactly means "LookIn:=xlValues"??
If Not c Is Nothing Then
'I guess this means If c has a value or not, right?
If OPE = "AM" Then
adrow = 0
Else
adrow = 18
End If
Cell.Offset(0, 2).Value = Cells(c.Row + adrow, 11)
Cell.Offset(0, 3).Value = Cells(c.Row + adrow + 1, 11)
Cell.Offset(0, 4).Value = Cells(c.Row + adrow + 2, 11)
Cell.Offset(0, 7).Value = Cells(c.Row + adrow + 12, 11)
'at C2004.xls I replace the values i need it
End If
End With
End If
Windows("C2004.xls").Activate
Next
End Sub

'(1) ----> here I want to select the value of the next column , for example if the cursor is positioning at B7 with value "03/07/2004" I would like to obtaint C7 value (only "AM" and "PM" is saved in this cells), then with this 2 values I need to look into a .DBF file located at c:\fax named faxdata.dbf wich has a column called datefax (type Date) and another column called AMPM (type Char 2) and a third column called Lamount (type Integer) and what I need is to copy this value into the next column in the same Sheet (D7), as I said b4, only works until date 09/07/2004, Ive tried everything I know (not much of course) and couldn't make it work. <-------'

Thank you

PE

 

[sebastienm]

Hi,

1.What is the type of your Data variable? Variant? Date? String?

If variant, try to explicitely define it as a date:
Dim Data as date

Still a problem?

2. I don't know how dbf is coding dates, but in the Find method, try:
Set c = .Find(DateValue(Data), LookIn:=xlValues)
Any luck?

 

[Ped_Esc]

Hi,

Ty for your help and time, I tried what you mentioned it but I got errors on both of them (Type Mismatch)

At C2004.xls the column B is defined as customized ('dd/mm/yy') and when I open faxdata.dbf file the column A is defined as Date *01/07/2004. However It works for the first 9 days, so I change the first row from 01/07/2004 to 10/07/2004 and It doesn't work at all.
I have to make it work because I have to make this replacement at the end of the month the whole year every year, and with many other files so It takes me a lot of wasted time.

I'd really appreciate your help.

Regards,

PE

 

[sebastienm]

What is the data type of your Data variable in your code?

Where is the Type Mismatch occurs?
If at the line
Data=Cell
that would mean that the value of cell is not understood as a date.

I would therefore persist with declaring
Dim Data as Date
which wold make sure an error occurs if the assigned value is not a date so that you can capture inaccuracies quickly.
Then, assign data with:
Data = CDate(Cell.Value)
instead of
Data = Cell

Let's go step by step.

 

[Ped_Esc]

Ty for your patience!!.

I did what you told me I declare Data as Date and then assigned Data = CDate(Cell.Value) and It works but stil until date 09/07/2004. No errors appeared.

Ill give you more information.

faxdata.dbf is like this
DATA HAMPM DF GK
01/07/2004 AM 52.00 182.00 ------value need it
01/07/2004 AM 72600.00 255000.00
01/07/2004 AM 37183.71 106881.84
01/07/2004 AM 652.05 2290.26
01/07/2004 AM 36531.66 104591.58
01/07/2004 AM 611360.00 1003260.00
01/07/2004 AM 270400.00 952510.00
01/07/2004 PM 5.00 3.00 ------value need it
01/07/2004 PM 268576.00 193000.00
01/07/2004 PM 6500.00 4800.00
01/07/2004 PM 262076.00 188200.00
01/07/2004 PM 2412.00 1733.28
01/07/2004 PM 58.38 43.11
01/07/2004 PM 2353.62 1690.17
01/07/2004 PM 881760.00 1574770.00
01/07/2004 PM 130000.00 41000.00
01/07/2004 PM 42000.00 468010.00
01/07/2004 PM 0.00 0.00
01/07/2004 PM 969760.00 1147760.00
02/07/2004 AM 40.00 265.00 ------value need it
02/07/2004 AM 57000.00 373900.00
02/07/2004 AM 24342.50 160253.21
02/07/2004 AM 509.84 3344.36
02/07/2004 AM 23832.66 156908.85
02/07/2004 AM 969760.00 1147760.00
02/07/2004 AM 80000.00 995700.00
02/07/2004 AM 304400.00 53000.00
02/07/2004 AM 0.00 68000.00
02/07/2004 AM 745360.00 2022460.00
02/07/2004 PM 2.00 0.00 ------value need it
02/07/2004 PM 59578.00 0.00
02/07/2004 PM 2200.00 0.00
02/07/2004 PM 57378.00 0.00
02/07/2004 PM 0.00 4000.00
02/07/2004 PM 532.90 0.00
02/07/2004 PM 19.68 0.00
02/07/2004 PM 513.22 0.00
02/07/2004 PM 745360.00 2022460.00
02/07/2004 PM 690000.00 165600.00
02/07/2004 PM 29000.00 274200.00
03/07/2004 AM 100.00 90.00 ------value need it
03/07/2004 AM 0.00 254400.00
03/07/2004 AM 0.00 9838890.00
03/07/2004 AM 0.00 88004.41
03/07/2004 AM 0.00 464900.00
03/07/2004 AM 734850.00 214500.00
03/07/2004 AM 0.00 0.00
03/07/2004 AM 671510.00 2159260.00
04/07/2004 AM 10.00 106.00 ------value need it
04/07/2004 AM 0.00 7269750.00
04/07/2004 AM 0.00 145600.00
04/07/2004 AM 0.00 7124150.00
......
......
......

C2004.xls is like this ....

FDate ampm Ope MReceived
01/07/04 a 52 182
01/07/04 p 5 3

02/07/04 a 40 265
02/07/04 p 2 0

03/07/04 a 100 90
04/07/04 a 10 106
.....
.....
.....

Values of columns Ope and Mreceived are picked from columns DF GK at the first coincidence

I hope this can give you a better idea of what im trying to do, Im starting to think that my code is wrong.....

Thanks again

PE

 

[sebastienm]

Okay, a few things we can try (but keep the previous changes Dim Data as Date ....)

1. Make sure the dates are really what you see.

You have them in format dd/mm/yyyy
Say A100 has 10/07/2004. In another cell, enter =A100 and format it as dd mmmm yyyy (4 'm's) so that you can make sure that 07 is displayed as July and is understood as month and not the English format mm/dd/yyyy

You could also have some entries like 10/07/2004 10:12:21 but the format shows only 10/07/2004. In that case, a "10/07/2004 10:12:21" entry is NOT equal to a "10/07/2004" entry. You can check by entering =VALUE(A1) all along the date column (if col A contains the dates). It should show no decimals. Or even,
=(VALUE(A1)=VALUE(TEXT(A1,"dd/mm/yyyy")))
should return TRUE in each row.

2. Use Debug features
--> Insert a breakpoint at
Set c = .Find(Data, LookIn:=xlValues) 'I found this example
i.e. just click in the margin in front of the line --> a red dot appears
From now on, when the execution reaches that lines, it will Pause
Each time it Pauses, to pursue the excution, press F5, and for a Line By Line execution press F8
--> After the line
Set c = .Find(Data, LookIn:=xlValues)
add a line
MsgBox Format(Data,"dd mmmm yyyy") & " -- " & Data
--> Watch Window
You can also drop a few variables (eg Data ) into a Watch Window to observe them during execution.

Anyway, run the macro. Press F5 when it pauses until the MSgBox displays the last good date (09/07/2004), then press F8 to do a Line By line Excution and check what happens in that loop (specially if it goes to the next loop for 10/07/2004) and in the next loop(10/07/2004 ie make sure it find c) on a line by line basis.
You can check values of variables in the Immediate window when the code pauses, eg:
? Data
? c.Address
(? = print)

Any luck in locating a potential problem?

 

[Ped_Esc]

Me again, I did what you told me and I could figure it out that when the For Each Sentence reach the 10/07/2004 date, doesn't get into the If Not c Is Nothing Then sentence just ignores it and then go to the next Cell.
About changing the format to dd mmmm yyyy it shows 10 July 2004 in both files but when I insert =Value(B7) the cell shows #¿NAME? in both files too, I also kept previous changes. And last thing the msgbox I added shows ............... "10 July 2004 -- 10/07/2004"

Now I tried to do something different and save my faxdata.dbf file as .xls and changed the format of my both files with dd/mmmm/yyyy and changed the name in the macro from faxdata.dbf to faxdata.xls but now the macro doesn't change any value at all, so I return to my .dbf files, hehe.

Thanks again for your patience!!

PE

 

[sebastienm]

ok
- the #¿NAME? is probably because of the name of that function is different in your language. I use the English version of Excel.
- the fact that it does NOT go in the
If Not c Is Nothing Then
when data is 10/07/2004, means it didn't find that value (c is nothing) in
Worksheets("faxdata").Range("A1:A1000")
So:
- are you sure the 10/07/2004 exists in Worksheets("faxdata").Range("A1:A1000")
- could it be located further in rows greater than 1000?
- manually, can you find such a value and tell me in which row it is? (in sheet faxdata)

we are getting there !

Regards,
Sébastien

 

[Ped_Esc]

Yes you're right im using spanish version, so i changed to =valor(B7) and showed my a number with no decimals on both files.

10/07/2004 begins at cell A290 then 18 rows down (A308) is 11/07/2004 and same with the rest except for dates wich has not only AM but PM.

Thanks again for your help!!.

PE

 

[sebastienm]

-What about
= (VALOR(faxdata!A290)=VALOR(07!B7) )
B7 or the one containing the 10/07/2004 in sheet 07
By the way, does it contain the year in yyyy format or in yy format?
What does the function returns? TRUE or FALSE ?

- another thing to try:
Set c = .Find(Format(Data,"dd/mm/yyyy"), LookIn:=xlValues)
or "dd/mm/yy" depending if the sheet shows 2004 or 04 as year.
This forces to match a String, not a date.

Hopefully we'll be more lucky today
Seb

 

[Ped_Esc]

First of all, sorry for delay, I was off for 2 days.

I tried = (VALOR(faxdata!A290)=VALOR(07!B7) ) and the result was TRUE

At C2004.xls the format is customized dd/mm/yy and when I open the faxdata.dbf file at properties it shows me Date *14/03/2001??, however everytime I changed and save to dd/mm/yyyy or dd/mm/yy when I reopen the file it comes back to *14/03/2001 ??. However I was trying to change the format of C2004.xls to dd/mm/yyyy but when I run the macro I got the same problem.

I also tried Set c = .Find(Format(Data,"dd/mm/yyyy"), LookIn:=xlValues) with both formats yyyy and yy but I couldn't see any results at all, I mean there's no changes from date 01/07 to 09/07, I made the debug as you taught me (thank you !!) and could find out that the program doesn't execute the "If Not c Is Nothing Then" bucle
It just skip it.
Maybe If I send you both files C2004.xls and faxdata.dbf so you can analize it better, What do you think?.

Once again, Thank you very much for your help and time

Best Regards,

PE

 

[sebastienm]

Yes, PE, you can send me the file. My email address is listed at the end of my profile on: http://www.experts-exchange.com/M_644077.html

 

[Ped_Esc]

Thank you Sebastien, I've just sent it to you. I hope you can find my problem.

Regards,

PE

 

[Ped_Esc]

Sebastien,
You Are The Best !!!!
As you told me, I made the changes and now is working for all dates!!. I also could change the macro to enlarge that "column". I still can't believe that this problem was caused by the size of that column.

Well, I guess this is the end, hehe!!.
Thank you very much once more for being so patient with me. I reaaaally appreciate it.

So long,

Pedro