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
www.chrisjterrell.com/excel-v...
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()
'sheet2.cells(1,1).currentregion.offset(1,0).clear
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
sheet2.select
End Sub
Пікірлер: 43
Chris, as always awesome videos. I recommend your posts to my students. Thanks for posting :)
@EverydayVBAExcelTraining
4 жыл бұрын
Thank you sir
Beginner here. You codes never cease to amaze me. So darn efficient, yet easy to understand. Thank you soooo much.
Had a For Loop with a for each that took 1828 seconds to run. Using arrays it now takes 92 seconds. Great info! Thanks!
Smashing...no bullshit, simple and explicit
this is what i needed, thank you so much
You helped me a lot thanks mate!
Great technic !! Thumbs Up !!
@EverydayVBAExcelTraining
4 жыл бұрын
Thanks!
Awesome!
@EverydayVBAExcelTraining
4 жыл бұрын
Thanks
Thank u sir for this awesome video
@EverydayVBAExcelTraining
3 жыл бұрын
Thanks for the comment I appreciate it.
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.
Thanks for the enjoyable lesson! One question what does the "ibox x 1" do exactly?
@EverydayVBAExcelTraining
3 жыл бұрын
Good question. An inputbox will return a text string. Multiplying it by 1 forces it to be a number
@WTG194
3 жыл бұрын
@@EverydayVBAExcelTraining I thought that's what you meant, but thanks so much for the response.
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?
@EverydayVBAExcelTraining
2 жыл бұрын
Yeah you can pull in the header.
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.
@EverydayVBAExcelTraining
4 жыл бұрын
If I am writing a macro for myself I will not declare variables (provably not the best example, I know). If I do declare variable I will declare them at the top of the sub. Hope that helps
@michelpilon8171
4 жыл бұрын
Hi Paul If you write OPTION EXPLICIT at the top of your module, then you have to declare your variables. However, if you don't have OPTION EXPLICIT at the top of your module, then declaring your variables is only optional. This is what Chris is doing here. It is good practice to always declare your variables. If Chris code is not working for you, check if you have the OPTION EXPLICIT at the top of your module and if so, then remove it. Cheers.
@paulcleary3289
4 жыл бұрын
@@michelpilon8171 Ah, I understand the Option Explicit thing better now thanks to your explanation. It was set for me as declared by default. The code now works after your explanation Michel. Many thanks and best wishes. Paul
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.
@EverydayVBAExcelTraining
4 жыл бұрын
It would look like this If (Range("B1").Value = Range("D14").Value) OR (Range("C1").Value = Range("D14").Value) Then Range("A1").Value = "Yes" End if
@ninamillagracia9889
4 жыл бұрын
@@EverydayVBAExcelTraining thank you very much! appreciate your help.
I was x=range y(1)+range z(1)
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.
@EverydayVBAExcelTraining
4 жыл бұрын
This one hard and admittedly I covered a lot. I try to keep my videos under 10 minutes. Maybe I should have took a little more time on this one
@EverydayVBAExcelTraining
4 жыл бұрын
That is also why I post the code in the description so everyone try it for themselves. But your point is noted an I always appreciate the comments. It makes me better 😀
@robertlohman8947
4 жыл бұрын
@@EverydayVBAExcelTraining Thanks for your consideration and response. This IS a great channel
Are you still active on here? I could really use some help. = )
@EverydayVBAExcelTraining
Жыл бұрын
Not as much as I used to be. Why what's up
@arampoo
Жыл бұрын
@@EverydayVBAExcelTraining getting ready to put my head through my desk working on some vba code for a spreadsheet. I'm just starting to learn and picking it up pretty fast but there's a few things that are just not clicking yet. I've tried to find the info but it seems i'm trying something Unique because i'm coming up short. I've built a BOM template based off a data table along with a couple macros that copy selected info onto a main page. Now I'm trying to build a Macro to take my list and consolidate the duplicate entries to a single line while retaining, and summing, the needed quantities for those duplicate entries. By, what I'm positive was, shear dumb luck, I've actually got the Math part working like I want it to but the other 13 columns not so much. I keep pecking away at it and learning but if you're interested in taking a look let me know how best to do that. I appreciate your time and REALLY dig the videos! The way you lay stuff out makes sense to me = )
@EverydayVBAExcelTraining
Жыл бұрын
You might not need VBA. You could use a combination of a Unique formula and a sumifs or countifs formula
@arampoo
Жыл бұрын
@@EverydayVBAExcelTraining I think I forgot to mention that I want all the magic to happen within the same range on the main sheet.
@EverydayVBAExcelTraining
Жыл бұрын
@@arampoo are you doing the calcs in the macro or using formulas
This is a creat video but too much data to compare, rather than just simple loop array of of 2 to 1 equation