Match 1st element in 2d array, match 1st element in 2nd array

Jandrew63

New Member
Joined
Feb 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I'm pretty green with vba and have spent some time trying to find a solution prior to posting here.
I have to 2 ( 2d) arrays( LiteArr and PlatArr) and would like to loop through the first dimension of the LiteArr and find a match in the first dimension of PlatArr. ( there is no possibility of a non match) Once match is found:
Test if the 1st element( value) of 2nd dimension of LiteArr is < 1st element( value) of 2nd dimension of PlatArr . (If true msgbox and exit sub) else ' minimum width
Continue loop : Test if the 2nd element( value) of 2nd dimension of LiteArr is > 2nd element( value) of 2nd dimension of PlatArr (If true msgbox and exit sub) else ' maximum width
Loop continues comparing remaining 3 elements for 3rd to 5th of both arrays
There is lots of examples of reading arrays back to a worksheet but i couldn't find anything comparing 2 arrays using offsets or lookups for 2 arrays.
All help would be greatly appreciated ... i tend to understand things better once I see the code and can use the watch /immediate windows to see what is happening .

Below is the code to populate arrays - i haven't attempted to write nested loop as i am unsure of how to code it

VBA Code:
Dim LiteArr() As Variant
Sheet6.Activate
LiteArr = Range("A2", Range("A2").End(xlDown).End(xlToRight))
'Erase LiteArr

Dim PlatArr() As Variant
Sheet4.Activate
PlatArr = Range("BG3:BL24")
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,403
VBA Code:
Dim i As Long, j As Long

For i = 1 To UBound(LiteArr, 1)
    For j = 1 To UBound(PlatArr, 1)
        If LiteArr(i, 1) = PlatArr(j, 1) Then
            If LiteArr(i, 2) <= PlatArr(j, 2) Then
                MsgBox LiteArr(i, 1), , "Minimum Width"
            ElseIf LiteArr(i, 2) > PlatArr(j, 2) Then
                MsgBox LiteArr(i, 1), , "Maximum Width"
            End If
            Exit For
        End If
    Next j
    If j > UBound(PlatArr) Then MsgBox LiteArr(i, 1), , "No Match Found"
Next i
 
Solution

Jandrew63

New Member
Joined
Feb 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello AlphaFrog,
After trying your code I realized that i hadn't adequately articulated what I was attempting to do . However, the ability to run your code and view what happens in break mode. I was able to glean a lot and adapt the code to do what was intended! Which is the best way for me to learn and remember for the future.
Thank you so much for taking the time to help me !

Here is the adapted code:
VBA Code:
Dim LiteArr() As Variant
Sheet6.Activate
LiteArr = Range("A2", Range("A2").End(xlDown).End(xlToRight))
Dim PlatArr() As Variant
Sheet4.Activate
PlatArr = Range("BG3:BL24")
Dim i As Long, j As Long
 For i = 1 To UBound(LiteArr, 1)
    If LiteArr(i, 1) = PlatArr(1, 1) Then
       If LiteArr(1, 2) < PlatArr(1, 2) Then
           MsgBox LiteArr(i, 1) & " Minimum width is " & PlatArr(1, 2)
         Exit For
       ElseIf LiteArr(1, 2) > PlatArr(1, 3) Then
            MsgBox LiteArr(i, 1) & " Maximum width is " & PlatArr(1, 3)
           Exit For
       ElseIf LiteArr(1, 3) < PlatArr(1, 4) Then
            MsgBox LiteArr(i, 1) & " Minimum height is " & PlatArr(1, 4)
          Exit For
       ElseIf LiteArr(1, 3) > PlatArr(1, 5) Then
            MsgBox LiteArr(i, 1) & " Maximum height is " & PlatArr(1, 5)
          Exit For
       ElseIf LiteArr(1, 4) > PlatArr(1, 6) Then
            MsgBox LiteArr(i, 1) & " Maximum sq.ft.is &  PlatArr(1,6).value"
          Exit For
       End If
    End If
Next i
Erase LiteArr
Erase PlatArr
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,135
Messages
5,640,321
Members
417,137
Latest member
Dashivas123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top