I've written a program that runs fine. But I want it to stop executing when
it gets to the line on the source worksheet which has "Monthly Totals" in
column H. I've tried every combination of "Do while", "Do Until", "Loop
While", "Loop Until", equals, does not equal, etc. that I can think of, but
the macro still executes on the "Montly Totals" row and pastes "Monthly
Totals" into the other worksheet. This seems like it should be simple, but
like most things in VBA, is anything but.

Do while rTotals < "Monthly Totals"
'test for Totals row, skip

Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
sTRID = rTRCell.Value
Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)
lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTotals = rTRCell.Offset(0, 7)
If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

lTRRow = lTRRow + 1

Loop

End Sub

Is there anyway to get it to loop through the rows but STOP running when it
sees "Montly Totals"? I guess I could just use an IF statement, but I
thought this would be a good place for a "Do While" or a "Do Until", but
they just don't work!
TIA

This should work

Do while rTotals.Value < "Monthly Totals"

Sure there is no other character(s) in the cell?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"salgud" wrote in message
...
I've written a program that runs fine. But I want it to stop executing
when
it gets to the line on the source worksheet which has "Monthly Totals" in
column H. I've tried every combination of "Do while", "Do Until", "Loop
While", "Loop Until", equals, does not equal, etc. that I can think of,
but
the macro still executes on the "Montly Totals" row and pastes "Monthly
Totals" into the other worksheet. This seems like it should be simple, but
like most things in VBA, is anything but.

Do while rTotals < "Monthly Totals"
'test for Totals row, skip

Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
sTRID = rTRCell.Value
Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)
lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTotals = rTRCell.Offset(0, 7)
If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

lTRRow = lTRRow + 1

Loop

End Sub

Is there anyway to get it to loop through the rows but STOP running when
it
sees "Montly Totals"? I guess I could just use an IF statement, but I
thought this would be a good place for a "Do While" or a "Do Until", but
they just don't work!
TIA

On Mon, 28 Jul 2008 16:43:13 +0100, Bob Phillips wrote:

This should work

Do while rTotals.Value < "Monthly Totals"

Sure there is no other character(s) in the cell?

I checked that, reentered the text in to be sure. I even copied and pasted
the text from the macro to the spreadsheet and vice versa to make sure both
are the same.
I added ".value" to the rTotals, but that didn't help.
After I posted, I decided to move the line that increments that counter up
to the top of the loop so lTRRow/rTotals would be incremented earlier,
thinking that might help. (I reset the initial value accordingly) It
didn't.
Here is the entire program:

Public Sub TribalInvCheck()
Dim wbTribalHist As Workbook
Dim wbTribalTR As Workbook
Dim wsTribalTR As Worksheet
Dim wsTribalHist As Worksheet
Dim rTRCell As Range
Dim lTRRow As Long
Dim lHistRow As Long
Dim rFoundID As Range
Dim sTRID As String
Dim rTribalHist As Range
Dim lHistCol As Long
Dim rHistStart As Range
Dim rTotals As Range
Dim rTRDates As Range

Set wbTribalHist = ThisWorkbook
Set wbTribalTR = ActiveWorkbook
Set wsTribalTR = ActiveSheet
Set wsTribalHist = wbTribalHist.Worksheets("Historical")

Set rTribalHist = wsTribalHist.Range("A3:IV150")

'Application.ScreenUpdating = False

If ThisWorkbook.Name = ActiveWorkbook.Name Then
MsgBox "Please do not run this macro from the workbook that contains it."
_
& Chr(10) & "Please select a Turnaround Report and then restart this
macro."
Exit Sub
End If


'rTRCell.Select

'wsTribalHist.Activate
'rFoundID.Select

lTRRow = 2
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
Set rTotals = rTRCell.Offset(0, 7)

wsTribalHist.Activate

'Do loop until totals column shows "Monthly Totals"
Do While rTotals.Value < "Monthly Totals"
'test for Totals row, skip

' rHistStart.Select
lTRRow = lTRRow + 1
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
sTRID = rTRCell.Value
Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)
lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTotals = rTRCell.Offset(0, 7)
' wsTribalTR.Activate
' rTotals.Select

If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

Loop

End Sub

I can't for the life of me figure out why it doesn't exclude the "Monthly
Totals" line.
Any other suggestions?

I can create data to make it run, it keeps falling over, so not much I can
do. I did notice this though


If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

Why isn't it checking fo 'Monthly Totals'?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"salgud" wrote in message
...
On Mon, 28 Jul 2008 16:43:13 +0100, Bob Phillips wrote:

This should work

Do while rTotals.Value < "Monthly Totals"

Sure there is no other character(s) in the cell?

I checked that, reentered the text in to be sure. I even copied and pasted
the text from the macro to the spreadsheet and vice versa to make sure
both
are the same.
I added ".value" to the rTotals, but that didn't help.
After I posted, I decided to move the line that increments that counter up
to the top of the loop so lTRRow/rTotals would be incremented earlier,
thinking that might help. (I reset the initial value accordingly) It
didn't.
Here is the entire program:

Public Sub TribalInvCheck()
Dim wbTribalHist As Workbook
Dim wbTribalTR As Workbook
Dim wsTribalTR As Worksheet
Dim wsTribalHist As Worksheet
Dim rTRCell As Range
Dim lTRRow As Long
Dim lHistRow As Long
Dim rFoundID As Range
Dim sTRID As String
Dim rTribalHist As Range
Dim lHistCol As Long
Dim rHistStart As Range
Dim rTotals As Range
Dim rTRDates As Range

Set wbTribalHist = ThisWorkbook
Set wbTribalTR = ActiveWorkbook
Set wsTribalTR = ActiveSheet
Set wsTribalHist = wbTribalHist.Worksheets("Historical")

Set rTribalHist = wsTribalHist.Range("A3:IV150")

'Application.ScreenUpdating = False

If ThisWorkbook.Name = ActiveWorkbook.Name Then
MsgBox "Please do not run this macro from the workbook that contains it."
_
& Chr(10) & "Please select a Turnaround Report and then restart this
macro."
Exit Sub
End If


'rTRCell.Select

'wsTribalHist.Activate
'rFoundID.Select

lTRRow = 2
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
Set rTotals = rTRCell.Offset(0, 7)

wsTribalHist.Activate

'Do loop until totals column shows "Monthly Totals"
Do While rTotals.Value < "Monthly Totals"
'test for Totals row, skip

' rHistStart.Select
lTRRow = lTRRow + 1
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
sTRID = rTRCell.Value
Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)
lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTotals = rTRCell.Offset(0, 7)
' wsTribalTR.Activate
' rTotals.Select

If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

Loop

End Sub

I can't for the life of me figure out why it doesn't exclude the "Monthly
Totals" line.
Any other suggestions?

Thanks for your reply.
On Mon, 28 Jul 2008 17:46:55 +0100, Bob Phillips wrote:

I can create data to make it run, it keeps falling over, so not much I can
What does "falling over" mean?

do. I did notice this though


If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

Why isn't it checking fo 'Monthly Totals'?

The main loop checks for "Monthly Totals", which is the last line in the
sheet. Above that are lines of entry data for each client, broken down and
summed by service providers. So this code determines if the line is a
subtotal, in which case it is just skipped, or if it is a line for
services, in which case the dates are copied to another spreadsheet.
So if column H contains a date, that date and the one to the left are
copied to another worksheet. If it contains a subtotal, it's skipped over.
And if it contains the Montly Total, the macro ends. I want it to end
without copying "Montly Totals" to the other sheet, if possible.

Okay, I thought it might be, but I mentioned it in case.

Falling over means that the code fails because it doesn't like the data I
setup.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"salgud" wrote in message
. ..
Thanks for your reply.
On Mon, 28 Jul 2008 17:46:55 +0100, Bob Phillips wrote:

I can create data to make it run, it keeps falling over, so not much I
can
What does "falling over" mean?

do. I did notice this though


If rTotals.Value < "Totals" Then
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
rTRDates.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
End If

Why isn't it checking fo 'Monthly Totals'?

The main loop checks for "Monthly Totals", which is the last line in the
sheet. Above that are lines of entry data for each client, broken down and
summed by service providers. So this code determines if the line is a
subtotal, in which case it is just skipped, or if it is a line for
services, in which case the dates are copied to another spreadsheet.
So if column H contains a date, that date and the one to the left are
copied to another worksheet. If it contains a subtotal, it's skipped over.
And if it contains the Montly Total, the macro ends. I want it to end
without copying "Montly Totals" to the other sheet, if possible.

On Mon, 28 Jul 2008 18:34:25 +0100, Bob Phillips wrote:

Okay, I thought it might be, but I mentioned it in case.

Falling over means that the code fails because it doesn't like the data I
setup.

thanks, Bob.
I figured it out. While the lTRRow counter was incrementing before each
loop, the other dependent variable, RTRCell was being re-calculated after
the loop restarted, so the test was happening too late. I moved the
resetting of the variable to the end of the loop, after the counter is
reset, and it works.