Thank you for the lesson. This teaches how to create 2D arrays. Once learnt, one may try this alternative mean for copying the data from sheet to array(dynamic ). This eliminates declaring some variables, using 2 nested loops and rewriting codes when number of company/manager increases. Dim Compinfo As Variant ' To be used as dynamic array Dim i as Integer ' Following code will create 2D array in Compinfo variable taking the values from range A6:B14 Compinfo = Sheet1.Range("A7").CurrentRegion.Value For i = 2 to UBound(Compinfo , 1) ' Compinfo(i, 1), Compinfo(i, 2) Next i
Hi, I have learned from Paul Kelly that assigning array is much simpler. You can just use such a code: dim arr as Variant arr = Range("A6").CurrentRegion And that's it!
You can copy the range to the array in one line instead of the loop: CompInfo = Range("A7:B14").Value. Before that you need to declare the array: Dim CompInfo As Variant
Helpful video. I added code at the end of the line for adding a new sheet. "Set ShNew = NewBook.Worksheets.Add(after:=Sheets(Sheets.Count))" This forces the tabs/sheets to be in order. Top to bottom on the list becomes left to right in the sheet order. Also, a line at the end could be added, "Sheets(1).Delete", to get rid of the blank sheet (given you adopted my above code addition for order).
Thanks Leila for a very clear explanations of building and using arrays in VBA:-)) I know that this is VBA lesson but I would like to throw in my two cents here. This particular case we can do without VBA. I think, in 1 - 1.5 minute :-)) And it doesn't matter how many worksheets we need to add.
Nice video with clear explanation. about creating Array! What exactly are the benefits of using an array.? This is also possible with a loop based on the cell values in Excel. (I am novice... :)) So hence the question, am a novice VBA so I am interested.
Hi Leila, Your videos are great..really easy to understand and follow..Can you also make a video on how to communicate to microsoft sharepoint via VBA..I am looking for some basic tasks like - Download a file / folder from SP to local drive, Upload file to SP, check for existence of file in particular folder of SP, count the number of files in specific folder of SP, etc
Hi Leila! Thak you very much for sharing your knowledge on VBA with your superb posts and channel. In particular, I found this one as a great example of 2 dimensional Array. However, code creates worksheets in an inverse order, that is to say, the first one is the "Pes" worksheet, wich is the last one item of the list. I have been trying: For r = 8 To 1 Step -1, but VBA gives me an error. However the same idea works with an 1 dimesional Array. Please, How could I try to obtain worksheets in its order? Thanks a lot...
Pablo Alonso: Try using ...Worksheets.Add(After:=Sheets(Sheets.Count)). This will add the new sheet to the end of the sheet tabs. Note: You'll still have "Sheet1" in the new book. You might want to write something to find if Sheet1 exists & if so, delete it. Have a great day!
Hi Leila, thank you for all your wonderful videos. I am hoping you could help me. I am using vba to and an array to break up a large list of over 300 names I want my array to have a size of 64 names unless the last name in the array matches the 65th name.
I don’t suppose we could use strings to refer to data, rather than numbers for the bounds? I have an input which is a strong, and I want to use that as the variable being used to store data in the array. Eg. Input “species=dog”, array(species) = array(species) + 1
thank you so much Ms. Leila kindly please let me know how to use barcode for the same product and make the qty bar add one as much as you scan the same product hope you understand what i mean. thanks again
You're welcome Ismail. Not sure about your query though - best is to post your specific question with a sample file either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
Hi Leila your videos are really nice my question is that we can do this by loop having 2 variables one will store the company name and second manager and then we can run the loop.. I don't have any knowledge about Ubound and Lbound can you tell.me the difference please please
I created first button and copy paste it to the Userform but it did not Prompt: "You already have a control Name XXX. Do you want to create a control array?" so my question is how do I create Control array of Command buttons. what are the steps to follow? Am I missing something? I am new to this Topic Please help
How can check if array array is empty or not because iam get error if ido like this and array not empty Dim arr as variant Arr = range ("A1"). Currentregion. Value If arr =empty then 🌹
Hello I am trying to find an vba code to copy and paste every range of rows like(A1:C19).copy and paste in word file as image then copy from (A20:C39) and paste in the same word file and so on for the rest of the table in excel. they have told me that I need to use the looping in excel but I don't know exactly how to do it …. please help if you can
Thank you for your comment :) Yes - we cover variant arrays separately in the course. The aim here is to be able to visualize a two dimensional array. I find that's easiest to do when referencing rows and columns....
Please how can I convert a List of cells to an Array e.g A 1 by 9 to a 3 by 3 array arrangement, such that the Array is linked to the List Data? Something like this- 1 2 3 4 5 6 7 8 9 To 123 456 789
I want to know, how to store data into an Array based on a Single or Multiple Conditions in Excel VBA? The below-mentioned Code gives me the correct answer, but when I write Preserve Keyword after Redim (at line number 028) it gives me an Error. Why does it happen? Is there anyway to use Strings.Filter Method with 2D Array based on a Single or Multiple Conditions to store only the Necessary Data into Array? Raw Data: ID Title Released Date Length Genre 001 Marvel's The Avengers 04-May-12 143 Action 002 The Dark Knight Rises 20-Jul-12 165 Action 003 The Hunger Games 23-Mar-12 142 Adventure 004 Skyfall 09-Nov-12 143 Action 005 The Hobbit: An Unexpected Journey 14-Dec-12 169 Fantasy 006 The Twilight Saga: Breaking Dawn Part 2 16-Nov-12 116 Awful 007 The Amazing Spider-Man 03-Jul-12 136 Action ========================================================== Code: 001 Option Explicit 002 003 Sub CopyActionFilms() 004 ThisWorkbook.Save 005 006 Sheet3.Range("G1").CurrentRegion.Offset(RowOffset:=1, ColumnOffset:=0).ClearContents 007 008 Dim LastRow As Long 009 LastRow = Sheet3.Range("A1").Offset(RowOffset:=0, ColumnOffset:=1).End(xlDown).Row 010 011 Dim LastColumn As Long 012 LastColumn = Sheet3.Range("A1").End(xlToRight).Column 013 014 Dim FilmDetails As Variant 015 FilmDetails = Sheet3.Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow - 1, ColumnSize:=LastColumn) 016 017 Dim RowCounter As Long 018 Dim ColumnCounter As Long 019 020 Dim ActionFilmCount As Long 021 ActionFilmCount = 1 022 023 Dim Check As Boolean 024 025 Dim ActionFilmDetails As Variant 026 027 For RowCounter = LBound(FilmDetails, 1) To UBound(FilmDetails, 1) 028 ReDim ActionFilmDetails(1 To ActionFilmCount, 1 To LastColumn) 029 030 Check = False 031 032 For ColumnCounter = LBound(FilmDetails, 2) To UBound(FilmDetails, 2) 033 If FilmDetails(RowCounter, 5) = "Action" Then 034 ActionFilmDetails(ActionFilmCount, ColumnCounter) = FilmDetails(RowCounter, ColumnCounter) 035 Sheet3.Range("G2").Offset(RowOffset:=ActionFilmCount - 1, ColumnOffset:=ColumnCounter - 1).Value = ActionFilmDetails(ActionFilmCount, ColumnCounter) 036 037 Check = True 038 Else 039 Rem Nothing is needed to do 040 End If 041 Next ColumnCounter 042 043 If Check Then ActionFilmCount = ActionFilmCount + 1 044 Next RowCounter 045 End Sub
Get access to the full Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/
best youtube chanel for excel, simple, easy and right to the point
😘
You explain things well, instead of simply showing off your knowledge. Very good.
Thanks for sharing your knowledge on arrays. Nice job Leila.
Thank you for the lesson. This teaches how to create 2D arrays. Once learnt, one may try this alternative mean for copying the data from sheet to array(dynamic ). This eliminates declaring some variables, using 2 nested loops and rewriting codes when number of company/manager increases.
Dim Compinfo As Variant
' To be used as dynamic array
Dim i as Integer
' Following code will create 2D array in Compinfo variable taking the values from range A6:B14
Compinfo = Sheet1.Range("A7").CurrentRegion.Value
For i = 2 to UBound(Compinfo , 1)
' Compinfo(i, 1), Compinfo(i, 2)
Next i
Great tip!
Hi, I have learned from Paul Kelly that assigning array is much simpler. You can just use such a code:
dim arr as Variant
arr = Range("A6").CurrentRegion
And that's it!
That’s exactly what I was thinking
Leila Nice Job! You are the only video that pops up which is truly working with an Array and not confusing a list of cells for a declared Array.
It was great example of 2 dimensional Array, which was quick well explained... 👌
Thank you. Glad you find the explanation easy to follow :)
Well explained on something that can be really tricky
Thank you Wyn - nice to see you here :)
Amei sua aula, você explica muito bem. Tem poucas aulas em português sobre esse conteúdo. Obrigo pela ajuda.
Thanks for the array fun : )
You're very welcome Mike.
The best tutorial on array that I have ever seen. You are great.
thanks Leila, extremely helpful and easy to understand. You are a great Instructor and beautiful lady. :)
Thanks for that! Great example and very well explained.
You can copy the range to the array in one line instead of the loop: CompInfo = Range("A7:B14").Value. Before that you need to declare the array: Dim CompInfo As Variant
Helpful video. I added code at the end of the line for adding a new sheet. "Set ShNew = NewBook.Worksheets.Add(after:=Sheets(Sheets.Count))" This forces the tabs/sheets to be in order. Top to bottom on the list becomes left to right in the sheet order. Also, a line at the end could be added, "Sheets(1).Delete", to get rid of the blank sheet (given you adopted my above code addition for order).
Great! VBA Queen
Very very nice video. Very well explained
Thank you Ashok. Glad you find it easy to follow.
Excellent
i love your videos
OMG SHE'S GORGEOUS!!!
Excellent teacher! I also take your courses on Udemy. Well done
Excellent. Thank you !
You're very welcome Paulo.
superb ms L
Struggling to understand what kind person would give a dislike to this video.
Excellent. Thank you very much :)
Thanks. Good example of Array
Thanks Leila for a very clear explanations of building and using arrays in VBA:-))
I know that this is VBA lesson but I would like to throw in my two cents here. This particular case we can do without VBA. I think, in 1 - 1.5 minute :-)) And it doesn't matter how many worksheets we need to add.
:) Agree - I wouldn't ever think of writing this code if I had to do this once. if I had to repeat it every week on a larger set, then probably yes...
Absolutely agree, Leila :-))
Thanks alot you guided me toi much
Nice video with clear explanation. about creating Array!
What exactly are the benefits of using an array.?
This is also possible with a loop based on the cell values in Excel. (I am novice... :))
So hence the question, am a novice VBA so I am interested.
thanks.
well done
Good one. You can use dictionary in VBA which is more useful than array
Hi Leila, Your videos are great..really easy to understand and follow..Can you also make a video on how to communicate to microsoft sharepoint via VBA..I am looking for some basic tasks like - Download a file / folder from SP to local drive, Upload file to SP, check for existence of file in particular folder of SP, count the number of files in specific folder of SP, etc
Thanks Manish - Glad you find the videos easy to follow. I added this to my list.
Hi Leila! Thak you very much for sharing your knowledge on VBA with your superb posts and channel. In particular, I found this one as a great example of 2 dimensional Array. However, code creates worksheets in an inverse order, that is to say, the first one is the "Pes" worksheet, wich is the last one item of the list.
I have been trying: For r = 8 To 1 Step -1, but VBA gives me an error. However the same idea works with an 1 dimesional Array. Please, How could I try to obtain worksheets in its order? Thanks a lot...
Pablo Alonso: Try using ...Worksheets.Add(After:=Sheets(Sheets.Count)). This will add the new sheet to the end of the sheet tabs. Note: You'll still have "Sheet1" in the new book. You might want to write something to find if Sheet1 exists & if so, delete it. Have a great day!
It's great explanation. I love u Laila.
Sooo useful! Thanks Leila :-)
You're very welcome Alex :)
Excellent lady
Hi Leila, thank you for all your wonderful videos. I am hoping you could help me. I am using vba to and an array to break up a large list of over 300 names I want my array to have a size of 64 names unless the last name in the array matches the 65th name.
Nice
Great video
Thank you. I'm glad you like it.
Merci
My pleasure.
thank you
You're very welcome Mohamed
I don’t suppose we could use strings to refer to data, rather than numbers for the bounds? I have an input which is a strong, and I want to use that as the variable being used to store data in the array. Eg. Input “species=dog”, array(species) = array(species) + 1
thank you so much Ms. Leila kindly please let me know how to use barcode for the same product and make the qty bar add one as much as you scan the same product hope you understand what i mean. thanks again
You're welcome Ismail. Not sure about your query though - best is to post your specific question with a sample file either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
How to do it in MS Access form level
Thanks Leila :)
You're very welcome John.
Hi Leila your videos are really nice my question is that we can do this by loop having 2 variables one will store the company name and second manager and then we can run the loop.. I don't have any knowledge about Ubound and Lbound can you tell.me the difference please please
Yes that will work too. Ubound is the upper limit of the array and Lbound is the lower limit. These are used to get the size of the array.
I created first button and copy paste it to the Userform but it did not Prompt:
"You already have a control Name XXX. Do you want to create a control array?"
so my question is how do I create Control array of Command buttons. what are the steps to follow? Am I missing something? I am new to this Topic Please help
👍👍👍👍👍👍👍
How can check if array array is empty or not because iam get error if ido like this and array not empty
Dim arr as variant
Arr = range ("A1"). Currentregion. Value
If arr =empty then 🌹
Dim Arr as variant
Arr = Range("A1").CurrentRegion.Value
if IsArray(Arr) = True then
( ... )
Else
( ....)
end if
People, don't use for loop, it's not fast. Just equal the range to some array and that's it.
Hello
I am trying to find an vba code to copy and paste every range of rows like(A1:C19).copy and paste in word file as image then copy from (A20:C39) and paste in the same word file and so on for the rest of the table in excel. they have told me that I need to use the looping in excel but I don't know exactly how to do it …. please help if you can
CompInfo = Range("A7:B14") works as well to populate the array directly (?)
Thank you for your comment :) Yes - we cover variant arrays separately in the course. The aim here is to be able to visualize a two dimensional array. I find that's easiest to do when referencing rows and columns....
Great
Spending more time with Leila than with the Mrs.... 8-)
Haha. I hope she'll understand :)
Hi mam
how can it make Dynamic?
WoW = Wonder Woman = Leila Gharani
Aswm
Please how can I convert a List of cells to an Array e.g A 1 by 9 to a 3 by 3 array arrangement, such that the Array is linked to the List Data? Something like this-
1 2 3 4 5 6 7 8 9
To
123
456
789
i = 1
For j = 1 to 9
Arr(i,j) = cells(1,j)
If(j / i > 3) then
i=i+1
End if
Next j
I want to know, how to store data into an Array based on a Single or Multiple Conditions in Excel VBA? The below-mentioned Code gives me the correct answer, but when I write Preserve Keyword after Redim (at line number 028) it gives me an Error. Why does it happen? Is there anyway to use Strings.Filter Method with 2D Array based on a Single or Multiple Conditions to store only the Necessary Data into Array?
Raw Data:
ID Title Released Date Length Genre
001 Marvel's The Avengers 04-May-12 143 Action
002 The Dark Knight Rises 20-Jul-12 165 Action
003 The Hunger Games 23-Mar-12 142 Adventure
004 Skyfall 09-Nov-12 143 Action
005 The Hobbit: An Unexpected Journey 14-Dec-12 169 Fantasy
006 The Twilight Saga: Breaking Dawn Part 2 16-Nov-12 116 Awful
007 The Amazing Spider-Man 03-Jul-12 136 Action
==========================================================
Code:
001 Option Explicit
002
003 Sub CopyActionFilms()
004 ThisWorkbook.Save
005
006 Sheet3.Range("G1").CurrentRegion.Offset(RowOffset:=1, ColumnOffset:=0).ClearContents
007
008 Dim LastRow As Long
009 LastRow = Sheet3.Range("A1").Offset(RowOffset:=0, ColumnOffset:=1).End(xlDown).Row
010
011 Dim LastColumn As Long
012 LastColumn = Sheet3.Range("A1").End(xlToRight).Column
013
014 Dim FilmDetails As Variant
015 FilmDetails = Sheet3.Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow - 1, ColumnSize:=LastColumn)
016
017 Dim RowCounter As Long
018 Dim ColumnCounter As Long
019
020 Dim ActionFilmCount As Long
021 ActionFilmCount = 1
022
023 Dim Check As Boolean
024
025 Dim ActionFilmDetails As Variant
026
027 For RowCounter = LBound(FilmDetails, 1) To UBound(FilmDetails, 1)
028 ReDim ActionFilmDetails(1 To ActionFilmCount, 1 To LastColumn)
029
030 Check = False
031
032 For ColumnCounter = LBound(FilmDetails, 2) To UBound(FilmDetails, 2)
033 If FilmDetails(RowCounter, 5) = "Action" Then
034 ActionFilmDetails(ActionFilmCount, ColumnCounter) = FilmDetails(RowCounter, ColumnCounter)
035 Sheet3.Range("G2").Offset(RowOffset:=ActionFilmCount - 1, ColumnOffset:=ColumnCounter - 1).Value = ActionFilmDetails(ActionFilmCount, ColumnCounter)
036
037 Check = True
038 Else
039 Rem Nothing is needed to do
040 End If
041 Next ColumnCounter
042
043 If Check Then ActionFilmCount = ActionFilmCount + 1
044 Next RowCounter
045 End Sub
Thank you