Two Different Columns of Dates

[Marie]

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").NumberFormat = "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").NumberFormat = "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

 

[Juan Sanchez]

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").NumberFormat = "mm/dd/yy"
Application.Worksheets("Dates").Columns("l").NumberFormat = "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

 

[Tom Ogilvy]

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

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

 

[Marie]

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]

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

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.