i found another posting along these lines which told me it's better to
go from the bottom up when adding & deleting rows, so i've changed
that.
the problem is this line:

Next rRow

when stepping thru & this row is highlighted, it shows the correct
number. intellisense on the 2nd loop above also shows the correct
number. however, when you step thru one more step, it switches to
"empty".
i'm at a loss.
susan



On Feb 28, 12:56 pm, "Susan" wrote:
i sledgehammered the ranges to work.
but now i'm having another (stupid) problem.

Option Explicit

Sub Chris_macro()

Dim rArticle As Range
Dim rHeight As Range
Dim rWeight As Range
Dim rRow As Variant
Dim myHeight As Variant
Dim myWeight As Variant
Dim myArticle As String
Dim myTotal As Long
Dim myRow As Integer
Dim InsertPoint As Long
Dim myLastRow As Long
Dim ws As Worksheet

Set ws = ActiveSheet

myLastRow = Cells(20000, 1).End(xlUp).Row

rRow = 2
InsertPoint = 6

Do Until rRow = myLastRow + 1
On Error Resume Next
For Each rRow In ws
myRow = rRow
myHeight = Range("c" & myRow).Value
myWeight = Range("d" & myRow).Value
myTotal = myHeight + myWeight
myArticle = Range("b" & myRow).Text
If myArticle < "bike" Then
'do nothing
End If
If myArticle = "bike" And myTotal = 5 Then
Range("a" & InsertPoint).EntireRow.Insert
Range("a:d" & myRow).Copy InsertPoint
Range("e" & InsertPoint) = myTotal
End If
MsgBox rRow **** = 2, correct
MsgBox rRow + 1 *** = 3, correct
rRow = rRow + 1 *** = 3, correct
MsgBox rRow *** = 3, correct
Next rRow *** = 3, correct
Loop

End Sub

the 1st time thru, it works fine.
and the last rRow is correct BUT when it loops back up to the top,
rRow is suddenly set as empty.
any ideas?
susan

On Feb 28, 12:30 pm, "Susan" wrote:



i'm stuck :(
so far i've got:

Option Explicit

Sub Chris_macro()

Dim rArticle As Range
Dim rHeight As Range
Dim rWeight As Range
Dim rRow As Variant
Dim myHeight As Integer
Dim myWeight As Integer
Dim myArticle As String
Dim myTotal As Long
Dim myRow As Integer
Dim InsertPoint As Long
Dim myLastRow As Long
Dim ws As Worksheet

Set ws = ActiveSheet

myLastRow = Cells(20000, 1).End(xlUp).Row

Set rArticle = ws.Columns("b")
Set rHeight = ws.Columns("c")
Set rWeight = ws.Columns("d")

rRow = 2
InsertPoint = 6

Do Until rRow = myLastRow + 1
On Error Resume Next
For Each rRow In ws
myRow = rRow
myHeight = Range(rHeight & myRow).Value
myWeight = Range(rWeight & myRow).Value
myTotal = myHeight + myWeight
myArticle = Range(rArticle & myRow).Text
If myTotal = 5 And myArticle = "bike" Then
Range("a" & InsertPoint).EntireRow.Insert
Range("a:d" & myRow).Copy InsertPoint
Range("e" & InsertPoint) = myTotal
End If
Next rRow
rRow = rRow + 1
Loop

End Sub

but there's a type mismatch somehow in setting myHeight & myWeight & i
can't figure it out.
i know this is probably a much larger macro than it needs to be,
but.........
maybe somebody else can finish it?
sorry - but i'll keep working on it
susan

On Feb 28, 11:55 am, "Susan" wrote:

now i've got it!
i'm working on it.
susan

On Feb 28, 11:36 am, wrote:

Hello Susan

for this reason maybe can use for loop statement, so that I can start
with summing from the second row. Like this
The value of the column "height" in the second row + the value in the
thirth row + the value in the fourth row, etc.
afterwards if the total value = 5, and on the same row = bike, then
copy the second row below.
Thanks voor your responses
Chris- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -