Matrix/array in vba

2.pngHi,

pfa a picture. I want to read data from the rows in the excel into a matrix/array in vba. Then i want to loop through the matrix and execute the macro for each row/record. For example, in the picture I have uploaded, I want to store 2 rows in a matrix and loop through each row to run a macro. Please provide code for this.
UT      NG            3      7
FI      BA            7      10

Thanks
camper12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Quick question post storage..can you help me understand what you are looking for?? Also if you can post a sample file with results before and after it will help to design a code for you accordingly..
camper12Author Commented:
I did post the excel snapshot. I am trying to get the rows in a matrix data form into vba so that I can loop through it. Note the values have different data types.
Saurabh Singh TeotiaCommented:
Camper12,

From the snapshot i get how does your data look like but i'm not sure post which what result you are looking for?? Or what you are trying to do here??

Saurabh...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

camper12Author Commented:
This is my code:

l2 = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
l3 = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
l4 = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row
lLB = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
lUB = ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row
lastRow = Max(lastRowClass2, lastRowClass3, lastRowClass4, lastRowLB, lastRowUB, lastRow)

Dim MyRange As String
Dim InputRecords As Variant
MyRange = "B1:D" & "lastRow"
InputRecords = Range(MyRange)

In this code I am trying to find the lastRow based on  Max(lastRowClass2, lastRowClass3, lastRowClass4, lastRowLB, lastRowUB, lastRow). Max does not work in vba. Is there some solution/alternative  to it?

Also, once I get last row, I want to put this in a variant and then loop the rows.

Thanks
Saurabh Singh TeotiaCommented:
You can just simply do this..

   lastrow = Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        

Open in new window


This will find automatically last filled row of your data..

Saurabh...
Kanti PrasadCommented:
Hi

I am not too sure what you are trying but here is the code to store 2 rows (A3:E4)  in a matrix and loop through each row  and change the data inside that matrix (I am just appending value 99)

Sub StoreMatrix()
    Dim MyArray, aSheet As Worksheet, rCnt As Long, cCnt As Integer
    Dim ValueChangedArray, ArrayValue As Variant, ChangedValue As Variant
       
    Set aSheet = ActiveSheet
    With ActiveSheet.Range("A3:E4")
    ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
    ReDim ValueChangedArray(1 To .Rows.Count, 1 To .Columns.Count)
    MyArray = Sheet1.Range("A3:E4").Value
       
    For rCnt = 1 To .Rows.Count

        For cCnt = 1 To .Columns.Count

            Debug.Print MyArray(rCnt, cCnt)
           ArrayValue = MyArray(rCnt, cCnt)
           ChangedValue = ArrayValue & 99
            ValueChangedArray(rCnt, cCnt) = ChangedValue
            Debug.Print ValueChangedArray(rCnt, cCnt)
    Next: Next
   
        End With
  End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.