sum value then insert new row
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 |
sum value then insert new row
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 |
sum value then insert new row
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 - |
sum value then insert new row
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 |
sum value then insert new row
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 |
sum value then insert new row
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 |
sum value then insert new row
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 |
sum value then insert new row
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 - |
sum value then insert new row HELP?
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 - |
sum value then insert new row HELP!!
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 - |
sum value then insert new row HELP!!
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 |
sum value then insert new row HELP!!
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 |
sum value then insert new row HELP!!
i really hope that :) and thanks for everthing
|
Please help with loop
anybody?
tia susan On Feb 28, 4:27 pm, wrote: i really hope that :) and thanks for everthing |
Please help with loop
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 |
Please help with loop
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 |
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com