In VBA I'd rather prefer to use collections or dictionaries instead arrays. Especially when it comes to manipulate data in classes. But for spilling data into range that seems nice. Thank you!
Hi ! can we split the type of an array into the column/rows ? For a start, when we declare an array, the *hole* array is of the type you declared it to be. Can we declare a specific row/column to be of a specific type different one from the other ?
Sir, is there any way to apply Strings.Filter method in 2 dimensional array in Excel VBA? I want to filter array on single or multiple conditions. Or, you can say, I want to store data into The Final Array (ActionFilmDetails) that meet the Condition (Single or Multiple). I've written the below-mentioned code. But, I can't understand, why this code gives me "Run-time Error'13': Type Mismatch" Error? 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: Option Explicit Sub CopyActionFilmsOnly() ThisWorkbook.Save
Dim LastRow As Long LastRow = Sheet3.Range("A1").Offset(RowOffset:=0, ColumnOffset:=1).End(xlDown).Row
Dim LastColumn As Long LastColumn = Sheet3.Range("A1").End(xlToRight).Column
Dim FilmDetails As Variant FilmDetails = Sheet3.Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow - 1, ColumnSize:=LastColumn)
Dim RowCounter As Long Dim ColumnCounter As Long
Dim ActionFilmDetails As Variant
For RowCounter = LBound(FilmDetails, 1) To UBound(FilmDetails, 1) For ColumnCounter = LBound(FilmDetails, 2) To UBound(FilmDetails, 2) If FilmDetails(RowCounter, 5) = "Action" Then ActionFilmDetails(RowCounter, ColumnCounter) = FilmDetails(RowCounter, ColumnCounter) Else Rem Nothing is needed to do End If Next ColumnCounter Next RowCounter End Sub
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? 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 CopyActionFilmsOnly() 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
![⭐ฮิตในTikTok!! ( หมูเด้ง MooDeng ) Ver. แดนซ์โจ๊ะๆ ReMix BY [ ดีเจกิต รีมิกซ์ ]](http://i.ytimg.com/vi/3krsyfWUeHU/mqdefault.jpg)
Finally, I found a video that made me understand arrays. Thanks for the great explanation!
A such perfectly video! It was exactly what I was looking for. Now I'm able to create TwoDimensional Arrays using Preserve. Thanks a lot!
I was searching for this type of explanation. Thanks dude !
Great Tutorial, helped me a lot!
Very helpful. Thank you very much
In VBA I'd rather prefer to use collections or dictionaries instead arrays. Especially when it comes to manipulate data in classes. But for spilling data into range that seems nice. Thank you!
Again and again I come back to your channel to get help with some solutions I find nowhere!
Thank you very much, this was very helpful
Very informative discussion 🙂
Nice video sirji. I loved it. I appreciate your work
Thank youuu!!!!!!!!! so much!
Hi ! can we split the type of an array into the column/rows ?
For a start, when we declare an array, the *hole* array is of the type you declared it to be. Can we declare a specific row/column to be of a specific type different one from the other ?
Thank you. Very helpful
Sir, is there any way to apply Strings.Filter method in 2 dimensional array in Excel VBA? I want to filter array on single or multiple conditions. Or, you can say, I want to store data into The Final Array (ActionFilmDetails) that meet the Condition (Single or Multiple). I've written the below-mentioned code. But, I can't understand, why this code gives me "Run-time Error'13': Type Mismatch" Error?
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:
Option Explicit
Sub CopyActionFilmsOnly()
ThisWorkbook.Save
Sheet3.Range("G1").CurrentRegion.Offset(RowOffset:=1, ColumnOffset:=0).ClearContents
Dim LastRow As Long
LastRow = Sheet3.Range("A1").Offset(RowOffset:=0, ColumnOffset:=1).End(xlDown).Row
Dim LastColumn As Long
LastColumn = Sheet3.Range("A1").End(xlToRight).Column
Dim FilmDetails As Variant
FilmDetails = Sheet3.Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow - 1, ColumnSize:=LastColumn)
Dim RowCounter As Long
Dim ColumnCounter As Long
Dim ActionFilmDetails As Variant
For RowCounter = LBound(FilmDetails, 1) To UBound(FilmDetails, 1)
For ColumnCounter = LBound(FilmDetails, 2) To UBound(FilmDetails, 2)
If FilmDetails(RowCounter, 5) = "Action" Then
ActionFilmDetails(RowCounter, ColumnCounter) = FilmDetails(RowCounter, ColumnCounter)
Else
Rem Nothing is needed to do
End If
Next ColumnCounter
Next RowCounter
End Sub
👍👍👍
my vba its not working can u help me please how i solve my problem
Really nice 👌 😍💋 💝💖❤️
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?
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 CopyActionFilmsOnly()
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