sum value then insert new row

[hai.tio]

How can I make for loop?
the conditions are: 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

 

[Susan]

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

 

[Susan]

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

 

[hai.tio]

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 are: 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

 

[Susan]

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

 

[hai.tio]

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

 

[Susan]

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

 

[Susan]

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

 

[Susan]

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

 

[Susan]

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

 

[hai.tio]

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

 

[Susan]

somebody will rescue us soon.

susan

 

[hai.tio]

i really hope that and thanks for everthing

 

[Susan]

anybody?
tia
susan

 

[hai.tio]

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

 

[Susan]

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