Use a For Loop and If Statement to loop through an Array and add data to another Sheet in VBA Code

Grab the Free VBA Quick Reference Guide
Master this process and you will will quickly become an Excel VBA Master.

Move data from one sheet to another using
CurrentRegion
Arrays
For Next Loops
Ubound
If Statement using criteria from an Input Box
And clearing a reporting sheet using a CurrentRegion.Offset(1,0).clear

This solution moves data from a master data set in Sheet1 to Sheet2.
We use CurrentRegion to take data from sheet one and put it into an array we then loop through that array with a for next loop using the Ubound of the array. Then to make it more fun we add an If statement to find specific rows that we want to move to Sheet2 based data from an input box. To do this we use another for loop and Ubound of the second index of the array (number of columns in the array). We delete the data at the beginning of the code and ad a button.

Code:
Sub LoopArray()

ibox = InputBox("Enter MPG over X", "MPG")

oarray = Sheet1.Cells(1, 1).CurrentRegion
rprw = 2
For rw = 2 To UBound(oarray)
If oarray(rw, 1) "GT" ibox Then
'Stop
For cl = 1 To UBound(oarray, 2)
Sheet2.Cells(rprw, cl) = oarray(rw, cl)
Next
rprw = rprw + 1
End If
Next

End Sub

[YvesAustin]

Chris, as always awesome videos. I recommend your posts to my students. Thanks for posting :)

[jasunderlan]

Had a For Loop with a for each that took 1828 seconds to run. Using arrays it now takes 92 seconds. Great info! Thanks!

[ninamillagracia]

I really like your videos and on how you explain it very well. however, i have here a simple code but i don't know how to insert in Loop
If Range("B1").Value OR Range("C1").Value = Range("D14").Value Then
Range("A1").Value = "Yes"
End If

I have over 1000 data. Please help me with this. thank you and i appreciate your videos and help.

[querrythis]

Beginner here. You codes never cease to amaze me. So darn efficient, yet easy to understand. Thank you soooo much.

[paulcleary]

Hi Chris, I am new to VBA and love your channel but struggled with this one and it seems so simple!. Do you declare your variable elsewhere as I cannot get the code to work for me. If so what are they declared as


Thanks and please keep up the good work.

[peterwooldridge]

Smashing...no bullshit, simple and explicit

[v.vivekthamilarasan]

Really superb! This is what I'm exactly looking for.. thanks!
Also, I have a doubt.. if I need to copy data based on header how will I do it in array? Could you help me with that part pls?

[alterchannel]

this is what i needed, thank you so much

[kathyachavez]

This is a creat video but too much data to compare, rather than just simple loop array of of 2 to 1 equation

[dccn]

Hi Chris. Is there a way to loop through an array and check if the current values are within a range of previous values, then run some code if true? I have some code that creates a collection of shape positions within a worksheet and stores them. I also have them transposed onto a separate sheet. I want to loop through the collection or sheet of data and determine if each subsequent item on the list is within any previous range. My intent is to move these shapes if they are overlapping each other in an automated fashion.

[robertlohman]

I have watched many of your videos and have learned a lot. This video however, is sped thru without explaining your variables and what they stand for. If you took your time it might have been better to understand. Maybe you should try Option Explicit.

[WTG]

Thanks for the enjoyable lesson! One question what does the "ibox x 1" do exactly?

[arampoo]

Are you still active on here? I could really use some help. = )