Multiple Condition If statement with loop

[bamboozle]

Hello,

Im new to VBA and have hit a wall trying to figure out how to write a if statement with multiple conditions to loop through a set of data. I'm teaching myself and the best way has been to look at code others have wrote and then modify it. However, I cant find anything that is similiar to what I need to do. Which is why I am posting here. Here is the scenario. I have a large data set with a header. I need to copy lines of data over to a new worksheet when certain conditions are met. Here is a sample set. Its housed in a sheet called "Modified Raw".

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Store	SKU	beg qty	end qty	rec	ret	found	sold	rem	lost	dam	dis	other
2	Name	0	0	0	0	0	0	0	0	0	0	0	0
3	Name	0	20	20	0	0	1	0	0	0	0	0	0
4	Name	0	0	0	0	0	0	0	0	0	0	0	0
5	Name	0	200	200	0	0	0	0	0	0	1	0	0

<tbody>
</tbody>

If any value in the range E - M is greater than 0, I need to copy the entire range (A-M) for that row and paste into a different worksheet titled "Import". So in the above example, the result would be row 3 and row 5 being pasted into "import". Pasting would start in cell b2, then move to b3, then b4 and so on until the entire data set in "Modified Raw" has been gone through. Its also probably important to mention there is a lot of rows of data in "Modified Raw". I've been using Lastrow in toher parts o my macro. For example, in the last set of data there are 23,881 rows. However, only about 2000 of those rows end up needing to be copied over to "Import".

Any help on this would be greatly appreciated. Thanks!

 

[oldbrewer]

for j=2 to 5 will look at each row in turn
for k=6 to 14 will look at each relevant column
if cells(j,k)>0 (finds values greater than 0) then (code to copy row)
next k
next j

this should get you started

 

[My Aswer Is This]

I will continue to monitor this thread and if you don't get an answer today I will give you an answer.

It would be best to do this with a filter instead of a loop.

But I'm not sure how to build a filter with multiple fields looking for one value of zero (0)
Maybe someone will show me how to do that.

Like if field1 or field2 or field3 value= "0"

I know how to do that with a loop but with 23,000 rows it would be better using a filter.

 

[bamboozle]

Here is what I ended up doing.

RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To RowCount
Range("n" & i).Select
check_value = ActiveCell
If check_value = "CHANGE" Then
ActiveCell.EntireRow.Resize(1, 13).Copy
Sheets("Import").Select
RowCount = Cells(Cells.Rows.Count, "b").End(xlUp).Row
Range("b" & RowCount + 1).Select
ActiveSheet.Paste
Sheets("Modified Raw").Select
End If
Next

Instead of going through each cell. hard coded a if statement into the spreadsheet that looks at the same rows and if any cell is greater than zero is reports back "CHANGE". Then in the macro, I look only through that column and if the cell value equals "CHANGE" it copies the row and pastes it in the other sheet. While it works, I'm not happy with the speed. For some reason it takes around 2 minutes to go through the one column to look for "CHANGE". As I watch the macro work, its almost like it goes through the same rows multiple times. Its hard to tell because its executing so fast. But I noticed it will go through rows 4000 and whatever, then move onto 5000, but circle back to 4000. It does that throughout. So I'm not sure what that is about.

I think My Answer Is This may be correct and using a loop is not best practice and a filter could be better/quicker. However, I cannot think of a way to filter. Essentially the filter would need filter out any row that has all zeros in E-M. Maybe I will research that next. If anyone has additional thoughts or is good with filters. Let me know.

 

[bamboozle]

Thanks. I think you may be correct about filter being a better route to take. Check out my post below to see what I ended up going with.

 

[My Aswer Is This]

I'm glad to see your learning how to do things with excel and not just here to get scripts written for you.
I believe I can write a script using a loop without using "select" but loops are slower then using a filter.
Lets wait till tomorrow and if no one hops on this tread with a filter solution I will write another script tomorrow using a loop. Unless I figure out how to write a Filter script with multiple field searches for Zero (0)

 

[My Aswer Is This]

You should try this looping script till I find a way to do it with a filter.

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets("Modified Raw").Activate

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("Import").Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 1 To Lastrow
If Cells(i, 5).Value > 0 Or _
Cells(i, 6).Value > 0 Or _
Cells(i, 7).Value > 0 Or _
Cells(i, 8).Value > 0 Or _
Cells(i, 9).Value > 0 Or _
Cells(i, 10).Value > 0 Or _
Cells(i, 11).Value > 0 Or _
Cells(i, 12).Value > 0 Or _
Cells(i, 13).Value > 0 Then

Range("A" & i & ":M" & i).Copy Destination:=Sheets("Import").Range("A" & Lastrowa)
Lastrowa = Lastrowa + 1
End If
Next

Application.ScreenUpdating = True
End Sub

 

[oldbrewer]

Essentially the filter would need filter out any row that has all zeros in E-M.

I thought a single zero was the criterion ?

and turn off autocalculate at start of macro, turn it back on at the end

 

[My Aswer Is This]

I realize that. Seems like there would be an easier way then writing 13 different. Field1 .value =0 or field2.value=0 or field 3 .value = 0 I would like to see someone write that code not just say write it. I don't know how to write it for the original poster or I would do it. I'm sure he does not know how to do it or he would not be here asking for help.

Essentially the filter would need filter out any row that has all zeros in E-M.

I thought a single zero was the criterion ?

and turn off autocalculate at start of macro, turn it back on at the end

 

[oldbrewer]

Store	SKU	beg qty	end qty	rec	ret	found	sold	rem	lost	dam	dis	other
Name1	sku1	0	0	0	0	0	0	0	0	0	0	0
name2	sku2	20	20	0	0	1	0	0	0	0	0	0
Name3	sku3	0	0	0	0	0	0	0	0	0	0	0
Name4	sku4	200	200	0	0	0	0	0	0	1	0	0
Name5	sku5	0	0	0	0	0	0	0	0	0	0	0
Name6	sku6	20	20	0	0	1	0	0	0	0	0	0
Name7	sku7	0	0	0	0	0	0	0	0	0	0	0
Name8	sku8	200	200	0	0	0	0	0	0	1	0	0
Name9	sku9	0	0	0	0	0	0	0	0	0	0	0
Name10	sku10	20	20	0	0	1	0	0	0	0	0	0
name2	sku2	20	20	0	0	1	0	0	0	0	0	0
Name4	sku4	200	200	0	0	0	0	0	0	1	0	0
Name6	sku6	20	20	0	0	1	0	0	0	0	0	0
Name8	sku8	200	200	0	0	0	0	0	0	1	0	0
Name10	sku10	20	20	0	0	1	0	0	0	0	0	0
			this macro has found rows where a value in col E through to column M is greater than 0
			and copied the row to the lower table
			Sum = 29
			For j = 2 To 11
			For k = 5 To 13
			If Cells(j, k) > 0 Then GoTo 100 Else GoTo 200
			100 Sum = Sum + 1
			Rows(j).Select
			Selection.Copy
			Rows(Sum).Select
			ActiveSheet.Paste
			GoTo 300
			200 Next k
			300 Next j
			End Sub

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>