Multiple Condition If statement with loop

bamboozle

New Member
Joined
Apr 13, 2016
Messages
15
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".

ABCDEFGHIJKLM
1StoreSKUbeg qtyend qtyrecretfoundsoldremlostdamdisother
2Name000000000000
3Name02020001000000
4Name000000000000
5Name0200200000000100

<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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,279
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

New Member
Joined
Apr 13, 2016
Messages
15
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

New Member
Joined
Apr 13, 2016
Messages
15
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,279
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,279
You should try this looping script till I find a way to do it with a filter.

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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,279
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
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
StoreSKUbeg qtyend qtyrecretfoundsoldremlostdamdisother
Name1sku100000000000
name2sku22020001000000
Name3sku300000000000
Name4sku4200200000000100
Name5sku500000000000
Name6sku62020001000000
Name7sku700000000000
Name8sku8200200000000100
Name9sku900000000000
Name10sku102020001000000
name2sku22020001000000
Name4sku4200200000000100
Name6sku62020001000000
Name8sku8200200000000100
Name10sku102020001000000
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>
 

Forum statistics

Threads
1,082,246
Messages
5,363,991
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top