How can I make for loop?
the conditions a if total value column height = 5 and column
Article = "bike" then copy second row to row 6 (therefore 1 row
added between file 5 and 6)
thanks for your response

columns don't have a height.................
do you mean row height or column width?
item will ALWAYS be added @ row 6?
susan

On Feb 28, 10:11 am, wrote:
How can I make for loop?
the conditions a if total value column height = 5 and column
Article = "bike" then copy second row to row 6 (therefore 1 row
added between file 5 and 6)
thanks for your response

additionally,

how many columns are you summing?
or are they rows?
is all the data to be searched for the condition below row 6?

you aren't being very clear & i can't decipher what you want....
what i SEE is

article=columnA
total = column B

for each row in worksheet
if range("article")="bike" and
if range("total")= what????? then
sum what?
insert row @ row 5
copy row found to row 6

maybe somebody else can figure it out the way you've got it, but
you've lost me.
susan


On Feb 28, 10:45 am, "Susan" wrote:
columns don't have a height.................
do you mean row height or column width?
item will ALWAYS be added @ row 6?
susan

On Feb 28, 10:11 am, wrote:



How can I make for loop?
the conditions a if total value column height = 5 and column
Article = "bike" then copy second row to row 6 (therefore 1 row
added between file 5 and 6)
thanks for your response- Hide quoted text -

- Show quoted text -

hello
no article height weight <--first row
1 motor 1 1 <--second row
2 motor tires 1 1
3 motor tires 1 1
4 motor tires 1 1
5 bike 1 1
6 motor tires 2 1
7 motor tires 1 1
8 motor tires 4 1
9 motor tires 1 1
10 motor tires 1 1
11 bike 1 1
12 motor tires 5 1
13 motor tires 1 1
14 motor tires 1 1
15 motor tires 2 1
16 bike 1 1
17 motor tires 1 1
18 motor tires 1 1
19 motor tires 1 1

excel file look like this.
How can I make loop for this
the conditions a if total value column height = 5 and column
Article = "bike" then copy second row to row 6 (therefore 1 row
added between file 5 and 6)
thanks for your response

ahhh. that's much better.
so basically you want the information sorted????
but you are asking for
"bike" AND height=5
none of the cells you listed qualify for that.
all of the "bike" entries have height=1.
susan


On Feb 28, 11:07 am, wrote:
hello
no article height weight <--first row
1 motor 1 1 <--second row
2 motor tires 1 1
3 motor tires 1 1
4 motor tires 1 1
5 bike 1 1
6 motor tires 2 1
7 motor tires 1 1
8 motor tires 4 1
9 motor tires 1 1
10 motor tires 1 1
11 bike 1 1
12 motor tires 5 1
13 motor tires 1 1
14 motor tires 1 1
15 motor tires 2 1
16 bike 1 1
17 motor tires 1 1
18 motor tires 1 1
19 motor tires 1 1

excel file look like this.
How can I make loop for this
the conditions a if total value column height = 5 and column
Article = "bike" then copy second row to row 6 (therefore 1 row
added between file 5 and 6)
thanks for your response

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

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

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 -

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 -

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 -

Hello Susan
thanks for your response and your effort, this have almost succeeded.
I have already been 3 days busy and I cannot solve it, it is too
difficult for me

somebody will rescue us soon.
:)
susan


On Feb 28, 4:04 pm, wrote:
Hello Susan
thanks for your response and your effort, this have almost succeeded.
I have already been 3 days busy and I cannot solve it, it is too
difficult for me

i really hope that :) and thanks for everthing

anybody?
tia
susan

On Feb 28, 4:27 pm, wrote:
i really hope that :) and thanks for everthing

Hello Susan
I made your script like this, this can summing the collumn 3 but how
can i use or in if condition?
Option Explicit
'value van column c optellen en plaats productkop na 51
Sub sikkens()

Dim rHeight As Integer
Dim rHeightvolg As Integer
Dim rRow As Range
Dim rowCount As Long
Dim ws As Worksheet
Dim itemDesign As String
Dim itemrow As Integer

Set ws = ActiveSheet


rHeight = 0
rowCount = 0


For Each rRow In ws.Rows
If rowCount 0 Then
If rRow.Cells(1).Value = "" Then
Exit For
End If
rHeight = rHeight + CInt(rRow.Cells(3).Value)
itemDesign = rRow.Cells(2).Text
itemrow = rRow.Cells(2).Row

'MsgBox rHeight & itemDesign & itemrow & bla

If rHeight = 51 And itemDesign = "ArtikelAfb" Or rHeight =
60 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(6).Value =
rRow(2).Cells(6).Text
ElseIf rHeight = 111 And itemDesign = "ArtikelAfb" Or
rHeight = 120 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(6).Value = rRow.Cells(6).Text
ElseIf rHeight = 171 And itemDesign = "ArtikelAfb" Or
rHeight = 180 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(6).Value = rRow.Cells(6).Text
ElseIf rHeight = 231 And itemDesign = "ArtikelAfb" Or
rHeight = 240 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(5).Value = rRow.Cells(5).Text
'MsgBox ("row num=" & rRow.Cells(1).Value)
'Exit For
End If

End If
rowCount = rowCount + 1
Next rRow

End Sub

you got further than i did!
i don't know what your conditions are that you want to "OR", but you
use "OR" in the same way you use "AND".

if condition1 AND (condition2 OR condition3) then

example:
If chkBoolean.Value = True And _
(optCouponsOnly.Value = True Or _
optSchedCoupons.Value = True) Then

so who was helping who, anyway? :)
i'm glad you got it worked out.
susan


On Mar 1, 9:59 am, wrote:
Hello Susan
I made your script like this, this can summing the collumn 3 but how
can i use or in if condition?
Option Explicit
'value van column c optellen en plaats productkop na 51
Sub sikkens()

Dim rHeight As Integer
Dim rHeightvolg As Integer
Dim rRow As Range
Dim rowCount As Long
Dim ws As Worksheet
Dim itemDesign As String
Dim itemrow As Integer

Set ws = ActiveSheet

rHeight = 0
rowCount = 0

For Each rRow In ws.Rows
If rowCount 0 Then
If rRow.Cells(1).Value = "" Then
Exit For
End If
rHeight = rHeight + CInt(rRow.Cells(3).Value)
itemDesign = rRow.Cells(2).Text
itemrow = rRow.Cells(2).Row

'MsgBox rHeight & itemDesign & itemrow & bla

If rHeight = 51 And itemDesign = "ArtikelAfb" Or rHeight =
60 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(6).Value =
rRow(2).Cells(6).Text
ElseIf rHeight = 111 And itemDesign = "ArtikelAfb" Or
rHeight = 120 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(6).Value = rRow.Cells(6).Text
ElseIf rHeight = 171 And itemDesign = "ArtikelAfb" Or
rHeight = 180 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(6).Value = rRow.Cells(6).Text
ElseIf rHeight = 231 And itemDesign = "ArtikelAfb" Or
rHeight = 240 Then
Rows(itemrow).Insert
Rows(rHeight - 1).Cells(1).Value = ""
Rows(rHeight - 1).Cells(2).Value = "ProductKop"
Rows(rHeight - 1).Cells(3).Value = "1"
Rows(rHeight - 1).Cells(4).Value = "1"
Rows(rHeight - 1).Cells(5).Value = "NEXTP"
Rows(rHeight - 1).Cells(5).Value = rRow.Cells(5).Text
'MsgBox ("row num=" & rRow.Cells(1).Value)
'Exit For
End If

End If
rowCount = rowCount + 1
Next rRow

End Sub