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!

 

[My Aswer Is This]

My script in Post # 7 does this.

Does any one know how to do this with a Auto Filter instead of a loop??

 

[tonyyy]

Sub FilterCopy()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long
Set ws1 = Sheets("Modified Raw")
Set ws2 = Sheets("Import")
LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

If ws1.AutoFilterMode = True Then ws1.AutoFilterMode = False
With ws1.Range("A1:N" & LastRow1)
    .AutoFilter
    .AutoFilter Field:=14, Criteria1:="CHANGE", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy ws2.Range("A" & LastRow2 + 1)
End With
ws1.AutoFilterMode = False

End Sub

Cheers,

tonyyy

 

[My Aswer Is This]

tonyyy:

This was the original posters quote:

"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 post #7 I wrote a script that does what he wants:

But I would like to write the script instead using a filter like in your previous post. But your filter script does not address the situation of checking fields E:M to see if they are greater then Zero.

Could you please show me how to write a filter script to check all the fields to see if they are greater then Zero

Thanks

 

[tonyyy]

MAIT...

In Post #4, the OP wrote that s/he "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"... Examining the code in Post #4 the OP references Column N as the column with the "CHANGE". That's why I wrote the script as I did.

If the OP did not hard code the IF statement into Column N, I probably would still follow a similar approach... only that I would create the IF statement in vba [=IF(SUM(E2:M2)>0,"Change","")] and populate Column N, run the autofilter/copy routine, then delete Column N.

Cheers,

tonyyy

 

[bamboozle]

Thanks everyone! I was working on other projects today and have not had a chance to implement any of your new suggestions. I'll try to give them all a shot and give some feedback.

 

[bamboozle]

Ideally I do not want to use the IF statement in column N. I only did that so I could check that column with a loop and get to the result desired versus having to check all columns E:M. It was a work around in order to finish the macro and look to improve later. To be honest, I didn't even think about using an autofilter on column N for "CHANGE" to filter out the data I dont want and then copy the rest over in bulk. I feel kinda silly now. Using this method may be the best. But I'd still love to learn how to do a more advanced filter that MAIT is referring to.

 

[My Aswer Is This]

Bambozzle. I thought the same thing that you created the "CHANGED" formula as just a work around. it sounds like no one knows how to do this using a filter and not a loop.
But I don't give up easily I will see if I can come up with a way.
I believe my script in post # 7 should work for now.

 

[tonyyy]

The code below adds the CHANGE formula to Column N (and later deletes it), then applies the autofilter/copy routine. Takes about 13 seconds on about 20,000 rows... not even enough time to get a coffee.

Sub FilterCopy2()
'Dim myTimer As Single
'myTimer = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long, i As Long
Set ws1 = Sheets("Modified Raw")
Set ws2 = Sheets("Import")
LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

If ws1.AutoFilterMode = True Then ws1.AutoFilterMode = False
For i = 2 To LastRow1
    ws1.Cells(i, 14).Formula = "=IF(SUM(E" & i & ":M" & i & ")>0,""CHANGE"","""")"
Next i
Application.Calculation = xlCalculationAutomatic

With ws1.Range("A1:N" & LastRow1)
    .AutoFilter
    .AutoFilter Field:=14, Criteria1:="CHANGE", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy ws2.Range("A" & LastRow2 + 1)
End With
ws2.Columns(14).Clear
ws1.AutoFilterMode = False
ws1.Columns(14).Clear
Application.ScreenUpdating = True
'myTimer = Timer - myTimer
'MsgBox Format(myTimer, "0.00")
End Sub

I'll be curious, @MAIT, what you find about autofiltering multiple fields.

Cheers,

tonyyy

 

[My Aswer Is This]

That worked Tony thanks for that. One reason I wanted to learn this is to help others. A lot of times when I write looping scripts for people I find others on the Forum telling me I should use filters and I do know how to write simple filter scripts but could not figure this one out.
Now I know how to do this.

I was not able to find anyway to due multiple filters on multiple fields.

I would bet Peter or Rick may have an answer but they have not commented on this thread.


Thanks Tonyyy