I have a spreadsheet that looks like this:

Sheet A
Column A Column B
begin end
2/16/2005 3/15/2005

ID Price


Sheet B
ColumnA ColumnB ColumnC
ID Date Price
C2134 1/15/2005 29.99
F2156 1/30/2005 14.99
R5432 2/14/2005 12.98
M4621 1/22/2005 13.49
U4832 3/14/2005 6.98
R5563 2/17/2005 11.99

I'm working on a macro that searches through the dates in sheet B. When
it finds a value between the dates in Sheet A, cells A1 & B1, it
extracts the ID and cost from Sheet B, col A and Col C and pastes those
values into the next available row in Sheet A under ID and price (Col A
& B), starting in row 5 (first blank row below ID and Price).
I've tried several things to get it to increment from the first cell in
Col B in Sheet B, but it never moves to B3, just stays on B2 and loops
endlessly.
In Sheet B, cells B2 to B7 are the named range "Date". I previously
tried to using just the range "B:B" (instead of a named range), but the
program wouldn't do the "next" then either.
Here is the code:

Sub ExtractByDate()

Dim dtBeginDate As Date, dtEndDate As Date
Dim strID As String
Dim sCost As Single
Dim rCurCell As Range
Dim strCurID As String
Dim curCost As Currency
Dim iCurRow As Integer
Dim bFlag As Boolean

dtBeginDate = Worksheets("A").Range("A2").Value
dtEndDate = Worksheets("A").Range("B2").Value
iCurRow = 5
Worksheets("B").Range("b2").Select
bFlag = False

Do Until ActiveCell.Value = ""
For Each rCurCell In Range("Date")
If ActiveCell.Value dtBeginDate _
And ActiveCell.Value < dtEndDate Then
strCurID = ActiveCell.Offset(-1, 0).Value
curCost = ActiveCell.Offset(1, 0).Value
Worksheets("A").Range(iCurRow, 1).Value = strCurID
Worksheets("A").Range(iCurRow, 2).Value = curCost
bFlag = True

Else

End If
If bFlag = True Then iCurRow = iCurRow + 1
Next
Loop
If bFlag = False Then MsgBox "No dates within Range", vbOKOnly

End Sub

I know the code is crude, I'm just learning VBA. I'm guessing it looks
like code from many years ago, which is when I did most of my
programming.
The main problem is that the "For each, Next" loop isn't "nexting". It
just stays on cell B2 and loops back around. How do I get it to move to
the next cell in the range?
I'd like to get this code working, so I can understand how more about
the basics of VBA. Then, I'd like suggestions on how to properly write
this program using VBA's full functionality. One of the things that I
find daunting about VBA is that it seems that everything has several
ways of doing the same thing. Too many choices is always a problem for
me. I'm easily confused!
I'd also like to know if there's a way to interupt and endless loop in
VBA, other than going to the Task Manager and terminating the program.
And yes, I know if you write good code, you shouldn't get endless
loops. But that's a ways away for me.
Can anyone help me with this?

This should help. You were not going to the next cell but look at the better
method where there are NO selections which slow down your code and make the
screen flicker unless you turn off screen updating.

Sub doloop()
Range("a2").Select
Do Until ActiveCell = ""
MsgBox ActiveCell.Address
ActiveCell.Offset(1).Select
Loop
End Sub

Sub doloopbetter()
For i = 2 To Cells(2, "a").End(xlDown).Row
MsgBox Cells(i, 1).Address
Next
End Sub

--
Don Guillett
SalesAid Software

"davegb" wrote in message
oups.com...
I have a spreadsheet that looks like this:

Sheet A
Column A Column B
begin end
2/16/2005 3/15/2005

ID Price


Sheet B
ColumnA ColumnB ColumnC
ID Date Price
C2134 1/15/2005 29.99
F2156 1/30/2005 14.99
R5432 2/14/2005 12.98
M4621 1/22/2005 13.49
U4832 3/14/2005 6.98
R5563 2/17/2005 11.99

I'm working on a macro that searches through the dates in sheet B. When
it finds a value between the dates in Sheet A, cells A1 & B1, it
extracts the ID and cost from Sheet B, col A and Col C and pastes those
values into the next available row in Sheet A under ID and price (Col A
& B), starting in row 5 (first blank row below ID and Price).
I've tried several things to get it to increment from the first cell in
Col B in Sheet B, but it never moves to B3, just stays on B2 and loops
endlessly.
In Sheet B, cells B2 to B7 are the named range "Date". I previously
tried to using just the range "B:B" (instead of a named range), but the
program wouldn't do the "next" then either.
Here is the code:

Sub ExtractByDate()

Dim dtBeginDate As Date, dtEndDate As Date
Dim strID As String
Dim sCost As Single
Dim rCurCell As Range
Dim strCurID As String
Dim curCost As Currency
Dim iCurRow As Integer
Dim bFlag As Boolean

dtBeginDate = Worksheets("A").Range("A2").Value
dtEndDate = Worksheets("A").Range("B2").Value
iCurRow = 5
Worksheets("B").Range("b2").Select
bFlag = False

Do Until ActiveCell.Value = ""
For Each rCurCell In Range("Date")
If ActiveCell.Value dtBeginDate _
And ActiveCell.Value < dtEndDate Then
strCurID = ActiveCell.Offset(-1, 0).Value
curCost = ActiveCell.Offset(1, 0).Value
Worksheets("A").Range(iCurRow, 1).Value = strCurID
Worksheets("A").Range(iCurRow, 2).Value = curCost
bFlag = True

Else

End If
If bFlag = True Then iCurRow = iCurRow + 1
Next
Loop
If bFlag = False Then MsgBox "No dates within Range", vbOKOnly

End Sub

I know the code is crude, I'm just learning VBA. I'm guessing it looks
like code from many years ago, which is when I did most of my
programming.
The main problem is that the "For each, Next" loop isn't "nexting". It
just stays on cell B2 and loops back around. How do I get it to move to
the next cell in the range?
I'd like to get this code working, so I can understand how more about
the basics of VBA. Then, I'd like suggestions on how to properly write
this program using VBA's full functionality. One of the things that I
find daunting about VBA is that it seems that everything has several
ways of doing the same thing. Too many choices is always a problem for
me. I'm easily confused!
I'd also like to know if there's a way to interupt and endless loop in
VBA, other than going to the Task Manager and terminating the program.
And yes, I know if you write good code, you shouldn't get endless
loops. But that's a ways away for me.
Can anyone help me with this?

Don,
Thanks for your reply. Your routines work very well. I still don't
understand why mine doesn't work at all. Why does the "For Next" loop
not change values. I thought if you entered those commands, it would at
least increment itself. Obviously, there's more to it than just having
a "For each" and a "Next". What makes a loop loop?

You were using activecell without changing the active cell.

--
Don Guillett
SalesAid Software

"davegb" wrote in message
ups.com...
Don,
Thanks for your reply. Your routines work very well. I still don't
understand why mine doesn't work at all. Why does the "For Next" loop
not change values. I thought if you entered those commands, it would at
least increment itself. Obviously, there's more to it than just having
a "For each" and a "Next". What makes a loop loop?

Thanks again, Don.
So the "for each, next" loop doesn't work on the active cell? What
should I have used instead?
Also, is there a way to interupt and endless loop besides the Task
Manager?

I've re-written the macro using your loop. But since there's no
"ActiveCell", I don't know how to test for the date values to see which
data to extract. How do you test when the cell you need to test is
never selected?

Figured it out! Thanks.

Post your code so we can see your final

--
Don Guillett
SalesAid Software

"davegb" wrote in message
oups.com...
Figured it out! Thanks.

The final version is:

Sub ExtractbyDate()
Dim i As Integer
dtBeginDate = Sheets("A").Range("A2").Value
dtEndDate = Worksheets("A").Range("B2").Value
iCurRow = 5
Sheets("B").Select
For i = 2 To Sheets("B").Cells(2, "b").End(xlDown).Row
If Cells(i, 2).Value dtBeginDate _
And Cells(i, 2).Value < dtEndDate Then
strID = Cells(i, 2).Offset(0, -1).Value
curCost = Cells(i, 2).Offset(0, 1).Value
Sheets("A").Cells(iCurRow, 1) = strID
Sheets("A").Cells(iCurRow, 2) = curCost
iCurRow = iCurRow + 1

End If
Next
If iCurRow = 5 Then
MsgBox "There were no matching dates", vbOKOnly
End If

I had to put in the "Sheets ("B").Select" to get it to run if I was in
sheet A when I ran it. I thought putting in the "For i = 2 To
Sheets("B").Cells(2, "b").End(xlDown).Row" would help it find Sheet B,
but it didn't.
At least it runs.
Thanks for all the help!

or from anwhere in the workbook with NO selections.

Sub extractbydate2()
With Sheets("b")
For i = 2 To .Cells(2, 2).End(xlDown).Row
If .Cells(i, 2) [a!a2] And .Cells(i, 2) < [a!b2] Then _
.Range(.Cells(i, 2), .Cells(i, 3)).Copy _
Sheets("a").Cells(Cells(65536, 1).End(xlUp).Row + 1, 1)
Next i
End With
End Sub

--
Don Guillett
SalesAid Software

"davegb" wrote in message
oups.com...
The final version is:

Sub ExtractbyDate()
Dim i As Integer
dtBeginDate = Sheets("A").Range("A2").Value
dtEndDate = Worksheets("A").Range("B2").Value
iCurRow = 5
Sheets("B").Select
For i = 2 To Sheets("B").Cells(2, "b").End(xlDown).Row
If Cells(i, 2).Value dtBeginDate _
And Cells(i, 2).Value < dtEndDate Then
strID = Cells(i, 2).Offset(0, -1).Value
curCost = Cells(i, 2).Offset(0, 1).Value
Sheets("A").Cells(iCurRow, 1) = strID
Sheets("A").Cells(iCurRow, 2) = curCost
iCurRow = iCurRow + 1

End If
Next
If iCurRow = 5 Then
MsgBox "There were no matching dates", vbOKOnly
End If

I had to put in the "Sheets ("B").Select" to get it to run if I was in
sheet A when I ran it. I thought putting in the "For i = 2 To
Sheets("B").Cells(2, "b").End(xlDown).Row" would help it find Sheet B,
but it didn't.
At least it runs.
Thanks for all the help!

Very impressive! Wish I could figure out how to do that.

Just break down each part to figure it out. Remember when you use WITH you
need to use the . before instead of sheets("b")

--
Don Guillett
SalesAid Software

"davegb" wrote in message
ups.com...
Very impressive! Wish I could figure out how to do that.