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 -