Define negative loop

[vba317]

I need to retotal a variable named R. I am not sure how to do this. I need it to count backwards. So an example would be I have a spreadsheet with 14 rows in it. I am running a macro that will add another 14 rows to the original 14 rows so the new total of rows should be 28 instead of 14. I want the recount to count until the beginning cell is blank. Any help would be appreciated.

Dim myNum8 As Double
myNum8 = Application.InputBox("Do you want to delete previous cells? 1 for yes, 2 for no", Type:=1, Title:="Starting Row", Default:=2)
'Type:=1 is for number

If myNum8 = 0 Then Exit Sub
If myNum8 = 1 Then
  Start = myNum1 - 6
            
    For DLoop1 = Start To LR
        Range("J" & DLoop1).Value = ""
        Range("K" & DLoop1).Value = ""
        Range("L" & DLoop1).Value = ""
        Range("M" & DLoop1).Value = ""
    Next DLoop1

For R = myNum1 To LR


'A Column Date
Range("A" & R & ":" & "A" & LR).Value = (myDate)

'B Column Test Type
Range("B" & R & ":" & "B" & LR).Value = (Test)

'C Column Work Order
Range("C" & R & ":" & "C" & LR).Value = (myNum5 & WorkOrder)

'D Column Code
If myNum2 = 1 Then Range("D" & myNum1).Value = UCase(myNum4)
If myNum3 < 4 Then Range("D" & R & ":" & "D" & LR).Value = ("PAQ3284")
If myNum3 > 3 And myNum3 < 6 Then Range("D" & R & ":" & "D" & LR).Value = ("PAQ3000")


'F Column Pass
Range("F" & R & ":" & "F" & LR).Value = ("1")

'H Column totals
Range("H" & R & ":" & "H" & R).Formula = "=F" & R & "+G" & R

Next R

End If


'Sum Function for H Column
'Recount Total Column

'If myNum8 = 1 Then
'Do Until ActiveCell.Value = Empty
'ActiveCell.Offset(1, 0).Select
'RecCount = RecCount - 1
'Loop

 

[SkipVought]

Hi,

I need it to count backwards

WHY?

Is there a reason that you are not using spreadsheet functions rather than VBA?

Please answer both questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

To loop backwards you may use a structure like:

For i = 10 to 1 step -1

Next i

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[vba317]

Skip,
Good to hear from you. I guess I am trying to include everything in my macro to save time. Currently when I add the lines to my spreadsheet from the macro I click on the total and redo the sum. This spreadsheet tracks my work output so at the beginning of the day I am not sure how much I am going to do, but every tray of work has 14 units in it so I know I will do that much. Some days I have to run the macro 3 times, sometimes up to 10 times. So the retotaling becomes a pain when I am running the macro 10 times and I have to retotal 10 times.
Why I have to count backwards is I have the macro inserts lines at a beginning point and an end point which is what R is about. LR is defined as the last row. So what I have to do is recalculate the starting row.
I hope this helps

 

[SkipVought]

=SUM or =SUMIF does it for you. I do not see the reason for looping forward or backward or VBA for that matter.

BTW, if you're putting your aggregations at the BOTTOM of your ranges, I'd seriously rethink that. It is much easier to put aggregations at the TOP, and they are always in the same place. My users like that feature as they do not have to hunt for the summary information.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}