Two Different Columns of Dates
 

I'm thinking this must be impossible becasue I haven't received any response. If there is anybody pretty knowlegeable and they know it is impossible or think it is would you please tell me and I will stop driving myself crazy trying to figure it out

1st Column 2nd column 3rd Column
3/31/04 $500.00 2/1/02
2/01/01 600.00 1/15/04

I want to pick up any dollar amount that falls within the 1qt 04, whether it is the first or third column. So I should pick up $1,100.00. I had a code that picked up the first column but of course somebody always has to change something, and now they tell me if the 3rd column has a 1qt 04 date they want it also.
This is the code I am trying to use.
If anybody would know of another site that would explain this or a book that I could read about this or ANYTHING, I would be forever greatful!!! Please

Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy"

For Each Cell In Range("NoteList")
Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the first date
Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the second date
If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <= Range("controldate") Then
If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <= Range("controldate") Then

Select Case Cell.Offset(0, 2) 'this being the amount

Two Different Columns of Dates
 

Here's a non VBA way... you'll need an extra column...

If your 1st Column Value is A1 and 2d column B1 an so on...

On the fourth column type this formula:

=IF(OR(IF(AND(A1=37987,A1<=38077),1,0)=1,IF(AND
(C1=37987,C1<=38077),1,0)=1),B1,0)

This will give you the dollar ammount if there is a first
1/4 of 2004 date on either the 1st or 3rd column then
simply add the total of this fourth column...

Regards Juan

Everithing is possible... almost!




-----Original Message-----
I'm thinking this must be impossible becasue I haven't
received any response. If there is anybody pretty
knowlegeable and they know it is impossible or think it is
would you please tell me and I will stop driving myself
crazy trying to figure it out

1st Column 2nd column 3rd Column
3/31/04 $500.00 2/1/02
2/01/01 600.00 1/15/04

I want to pick up any dollar amount that falls within the
1qt 04, whether it is the first or third column. So I
should pick up $1,100.00. I had a code that picked up the
first column but of course somebody always has to change
something, and now they tell me if the 3rd column has a
1qt 04 date they want it also.
This is the code I am trying to use.
If anybody would know of another site that would explain
this or a book that I could read about this or ANYTHING, I
would be forever greatful!!! Please

Application.Worksheets("Dates").Columns("g").Numb erFormat
= "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").Numb erFormat
= "mm/dd/yy"

For Each Cell In Range("NoteList")
Cell.Offset(0, 5).Value = Cell.Offset(0,
5).Value 'this being the first date
Cell.Offset(0, 10).Value = Cell.Offset(0,
10).Value 'this being the second date
If Cell.Offset(0, 5) Range("HistDate") And
Cell.Offset(0, 5) <= Range("controldate") Then
If Cell.Offset(0, 10) Range("HistDate") And
Cell.Offset(0, 10) <= Range("controldate") Then

Select Case Cell.Offset(0, 2) 'this being the
amount

.

Two Different Columns of Dates
 

do you have to use a macro

=SUM(IF((A1:A10=DATEVALUE("1/1/04"))*(A1:A10<=DATEVALUE("3/31/04"))+(C1:C10
=DATEVALUE("1/1/04"))*(C1:C10<=DATEVALUE("3/31/04"))0,B1:B10))

Entered with Ctrl+shift+Enter should do it.

--
Regards,
Tom Ogilvy



"Marie" wrote in message
...
I'm thinking this must be impossible becasue I haven't received any
response. If there is anybody pretty knowlegeable and they know it is
impossible or think it is would you please tell me and I will stop driving
myself crazy trying to figure it out

1st Column 2nd column 3rd Column
3/31/04 $500.00 2/1/02
2/01/01 600.00 1/15/04

I want to pick up any dollar amount that falls within the 1qt 04, whether
it is the first or third column. So I should pick up $1,100.00. I had a code
that picked up the first column but of course somebody always has to change
something, and now they tell me if the 3rd column has a 1qt 04 date they
want it also.
This is the code I am trying to use.
If anybody would know of another site that would explain this or a book
that I could read about this or ANYTHING, I would be forever greatful!!!
Please

Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy"

For Each Cell In Range("NoteList")
Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the
first date
Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the
second date
If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <=
Range("controldate") Then
If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <=
Range("controldate") Then

Select Case Cell.Offset(0, 2) 'this being the amount

Two Different Columns of Dates
 

Thanks for your help! But, I have to use a macro because there is other select criteria I have to pick up after the two amounts are pulled (that I have set up as Case 302 etc) for example this is right below the selection of the two columns:
Case 302 To 303, 400 To 403 'Company 1
' CO200 = CO200 + cell.Offset(0, 1).Value
' If cell.Offset(0, 6) = 30 And cell.Offset(0, 9) < "X" Then
' CO200del = CO200del + cell.Offset(0, 1).Value
' End If
I just need to know the line I need to add to pick up the second group before all the select criteria , or am I all wet in thinking it can be done that way? I thought if it was before the case selection it would automatically add it to - for example, CO200.
"Tom Ogilvy" wrote:

do you have to use a macro

=SUM(IF((A1:A10=DATEVALUE("1/1/04"))*(A1:A10<=DATEVALUE("3/31/04"))+(C1:C10
=DATEVALUE("1/1/04"))*(C1:C10<=DATEVALUE("3/31/04"))0,B1:B10))

Entered with Ctrl+shift+Enter should do it.

--
Regards,
Tom Ogilvy



"Marie" wrote in message
...
I'm thinking this must be impossible becasue I haven't received any
response. If there is anybody pretty knowlegeable and they know it is
impossible or think it is would you please tell me and I will stop driving
myself crazy trying to figure it out

1st Column 2nd column 3rd Column
3/31/04 $500.00 2/1/02
2/01/01 600.00 1/15/04

I want to pick up any dollar amount that falls within the 1qt 04, whether
it is the first or third column. So I should pick up $1,100.00. I had a code
that picked up the first column but of course somebody always has to change
something, and now they tell me if the 3rd column has a 1qt 04 date they
want it also.
This is the code I am trying to use.
If anybody would know of another site that would explain this or a book
that I could read about this or ANYTHING, I would be forever greatful!!!
Please

Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy"

For Each Cell In Range("NoteList")
Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the
first date
Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the
second date
If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <=
Range("controldate") Then
If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <=
Range("controldate") Then

Select Case Cell.Offset(0, 2) 'this being the amount

Two Different Columns of Dates
 

Dim q1LB as Date, q1UB as Date
Dim cell as Range
q1LB = DateValue("1/1/2004")
q1UB = DateValue("3/31/2004")

for each cell in range(??)


if cell.offset(0,offset to column1).Value = q1LB and _
cell.offset(0,offset to column1).Value <= q1UB _
or _
cell.offset(0,offset to column2).Value = q1LB and _
cell.offset(0,offset to column2).Value <= q1UB then
select Case cell.Value
Case 302 To 303, 400 To 403 'Company 1
' CO200 = CO200 + cell.Offset(0, 1).Value
' If cell.Offset(0, 6) = 30 And cell.Offset(0, 9) < "X"
Then
' CO200del = CO200del + cell.Offset(0, 1).Value
' End If

End Case
End if

Next

the above pseudocode may give you some insights.

--
Regards,
Tom Ogilvy


"Marie" wrote in message
...
Thanks for your help! But, I have to use a macro because there is other
select criteria I have to pick up after the two amounts are pulled (that I
have set up as Case 302 etc) for example this is right below the selection
of the two columns:
Case 302 To 303, 400 To 403 'Company 1
' CO200 = CO200 + cell.Offset(0, 1).Value
' If cell.Offset(0, 6) = 30 And cell.Offset(0, 9) < "X"
Then
' CO200del = CO200del + cell.Offset(0, 1).Value
' End If
I just need to know the line I need to add to pick up the second group
before all the select criteria , or am I all wet in thinking it can be done
that way? I thought if it was before the case selection it would
automatically add it to - for example, CO200.
"Tom Ogilvy" wrote:

do you have to use a macro


=SUM(IF((A1:A10=DATEVALUE("1/1/04"))*(A1:A10<=DATEVALUE("3/31/04"))+(C1:C10
=DATEVALUE("1/1/04"))*(C1:C10<=DATEVALUE("3/31/04"))0,B1:B10))

Entered with Ctrl+shift+Enter should do it.

--
Regards,
Tom Ogilvy



"Marie" wrote in message
...
I'm thinking this must be impossible becasue I haven't received any
response. If there is anybody pretty knowlegeable and they know it is
impossible or think it is would you please tell me and I will stop
driving
myself crazy trying to figure it out

1st Column 2nd column 3rd Column
3/31/04 $500.00 2/1/02
2/01/01 600.00 1/15/04

I want to pick up any dollar amount that falls within the 1qt 04,
whether
it is the first or third column. So I should pick up $1,100.00. I had a
code
that picked up the first column but of course somebody always has to
change
something, and now they tell me if the 3rd column has a 1qt 04 date they
want it also.
This is the code I am trying to use.
If anybody would know of another site that would explain this or a
book
that I could read about this or ANYTHING, I would be forever greatful!!!
Please

Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy"

For Each Cell In Range("NoteList")
Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being
the
first date
Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being
the
second date
If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <=
Range("controldate") Then
If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10)
<=
Range("controldate") Then

Select Case Cell.Offset(0, 2) 'this being the amount