0

I have two tables like figure. If tables have same values, second table's column index should be same with first table. I need a vba code which fills second table's matching index column.

Figure

Taha Er
  • 1
  • 1
  • Can you show us what you tried by your own? Even if it does not do what you need. Then, when you say "tables" do you mean `listObject`s or two ranges, as we can see in the above picture? Is it possible that many occurrences of the same row content to be matched? – FaneDuru Sep 21 '22 at 07:45
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Sep 21 '22 at 15:10

1 Answers1

0

Please, test the next code. You did not answer my clarification questions and it assumes that the mentioned "tables" are the ranges we can see in your picture and there may be more occurrences of the same row content of the first range in the second one. If only one occurrence is possible and ranges to be compared are large, the code becomes faster if after arrMtch(j, 1) = i will be placed :Exit For, to exit the second loop:


Sub MatchingRangesRows()
   Dim sh As Worksheet, lastR As Long, arr1, arr2, arrMtch, i As Long, j As Long
   
   Set sh = ActiveSheet
   lastR = sh.Range("B" & sh.rows.count).End(xlUp).row
   
   arr1 = sh.Range("B2:D" & lastR).Value2 'place the range in an array for faster iteration/processing
   arr2 = sh.Range("G2:I" & lastR).Value2
   
   ReDim arrMtch(1 To UBound(arr2), 1 To 1) 'redim the matching array ass arr1  number of rows
   
   For i = 1 To UBound(arr1)
        For j = 1 To UBound(arr2)
            If arr1(i, 1) & arr1(i, 2) & arr1(i, 3) = _
                   arr2(j, 1) & arr2(j, 2) & arr2(j, 3) Then
                    arrMtch(j, 1) = i
            End If
        Next j
   Next i
   sh.Range("J2").Resize(UBound(arrMtch), 1).Value2 = arrMtch
End Sub

Please send some feedback after testing it.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Sorry, I didn't see your question. What I mean with Tables is that: first table is A1:D7 second table is G1:J7. Your code is working well thank you very much. – Taha Er Sep 21 '22 at 08:30
  • @TahaEr Welcome to SO; allow me a hint as new contributor: help other users to identify a good or helpful answer by marking the (best) solution as accepted by ticking the green checkmark shown on top of the relevant answer - see [Someone answers](https://stackoverflow.com/help/someone-answers) and [Accepting Answers: How does it work?](https://meta.stackexchange.com/a/5235). – T.M. Sep 21 '22 at 09:43