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

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?

--
Regards,
Sébastien


"Ped_Esc" wrote:

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

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" wrote:

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?

--
Regards,
Sébastien


"Ped_Esc" wrote:

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

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.
--
Regards,
Sébastien


"Ped_Esc" wrote:

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" wrote:

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?

--
Regards,
Sébastien


"Ped_Esc" wrote:

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

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" wrote:

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.
--
Regards,
Sébastien


"Ped_Esc" wrote:

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" wrote:

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?

--
Regards,
Sébastien


"Ped_Esc" wrote:

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

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?
--------------------------------------------------------------------------------------
--
Regards,
Sébastien


"Ped_Esc" wrote:

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