array loop that loops through another array

[Bob1980]

Good Evening,

Can someone help me out with this one? I am trying to look for matching values from column A within column B, and if a match is found; then to bring the value of Column B + 1 column over to Column A + 1 column. The way I am trying to do this is with two arrays, where each array is representative of column A and column B. Below is what I have.

Sub UserFormTest()

Dim i As Variant
Dim h As Variant
Dim j() As Variant
Dim k As Integer
Dim l As Integer
Dim m() As Variant


k = Application.WorksheetFunction.Count(Sheet1.Range("g5:g10000")) + 4
l = Application.WorksheetFunction.Count(Sheet1.Range("m5:m10000")) + 4

    'Here I'm trying to allocate value to the arrays
    j = Range(Cells(5, 7), Cells(k, 7))
    m = Range(Cells(5, 13), Cells(l, 13))
    
    'This is where I just want to loop through rank i of array(m) through all of the ranks of array(j)
    For i = 1 To UBound(m)
        For h = 1 To UBound(j)
            If m(i) = j(h) Then
                Sheet1.Range(Cells(4 + i, 13)).Value = Sheet1.Range(Cells(4 + h, 7)).Value
            End If
        Next h
    Next i
    
    'Test Cells to make sure my UBound objects are behaving correctly
    Sheet1.Cells(30, 3).Value = UBound(j)
    Sheet1.Cells(31, 3).Value = UBound(m)

End Sub

I'm getting an error '9', Subscript out of range error

If anyone can help me out on this, I'll greatly appreciate it. This forum has really assisted me with getting my feet wet with VBA.

Thanks,
Bob

[JBeaucaire]

That error usually means an object you've named can't be found. Perhaps instead of referencing Sheet1 you need to reference Sheets("Sheet1")?

Sheets("Sheet1").Range("g5:g10000"))

[Bob1980]

That didn't have any affect, the error is occuring at:

            If m(i) = j(h) Then

I also changed the for loop to:

            0 to UBound(whatever)

What I wanted to do is to use the For loop to loop through the array ranks with the variables i & h. But then, something is not translating over from the array to the i & h array bounds for that particular iteration in the loop. I'm going to try to use the "For Each" loop, but am still unsure how to pull the value out; let alone use the offset method to carry the value of the matching adjacent cell over to the other column.

Through researching this issue, I found another person with a similar problem; and one person responded by suggesting to use the .Find method. I'm going to look into that.

[mike7952]

can you upload a copy of your workbook, it might be easier to help find a solution.

[Kyle123]

Range arrays are 2 dimensional, you either need to transpose or loop through the specific dimension

If m(i,1) = j(h,1)

[Norie]

j and m are 2 dimensional loops.

Here you are trying to refer to items in them using only one index.

If m(i) = j(h) Then

[Bob1980]

Sure, here it is.

[Kyle123]

Is there an echo in here?

[Norie]

As welll as being 2 dimensional the arrays have 1- based indices.

For example j is 1 to 10, 1 to 1 and m is 1 to 6, 1 to 1.

This will compile and run.

    For i = 1 To UBound(m)
        For h = 1 To UBound(j)
            If m(i, 1) = j(h, 1) Then
                Sheet1.Cells(4 + i, 13).Value = Sheet1.Cells(4 + h, 7).Value
            End If
        Next h
    Next i

[Bob1980]

Hi Norie,

After reading the response about the dimension of the array, I tried this, lol:

    ReDim m(1 To l, 1 To 1)
    ReDim j(1 To k, 1 To 1)
    For i = 0 To UBound(m)
        For h = 0 To UBound(j)
            If UBound(m, i) = UBound(j, h) Then
                Sheet1.Range(Cells(4 + i, 13)).Value = Sheet1.Range(Cells(4 + h, 7)).Value
            End If
        Next h
    Next i

I'll go ahead and run yours. be right back.

[Bob1980]

Hey Everyone,

Thanks for all of the help. I changed the column references in the Cells object one more column over, cause that was my fault from the beginning.

I think the difference between my last attempt and Norie's suggestion is that I was trying to loop through the array by defining the dimension as the variable. But, Nories is doing the samething and taking into account the pointer variable, and using the 1 in,

m(i,1)

to point to the rank...or something like that. Big ups to all.
Ah crap, also; I tried to give "Reputation" to everyone involved, but the site won't let me. Sorry.

Thanks,
Bob

[mike7952]

See if this is what you what. Im guessing that you want to add the letter value for the number in column N

Option Explicit

Sub Example()
 Const sh As String = "sheet1"
 Dim aArr, i As Long
 
 With Worksheets(sh)
    aArr = .Range("g5", .Cells(Rows.Count, "g").End(xlUp).Offset(, 1)).Value
 End With
 
 With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(aArr)
        If Not .exists(aArr(i, 1)) Then
            .Item(aArr(i, 1)) = aArr(i, 2)
        End If
    Next
    With Worksheets(sh)
       aArr = .Range("m5", .Cells(Rows.Count, "m").End(xlUp).Offset(, 1)).Value
    End With
    For i = 1 To UBound(aArr)
        If .exists(aArr(i, 1)) Then
             aArr(i, 2) = .Item(aArr(i, 1))
        End If
    Next
 End With
 With Worksheets(sh)
    .Range("m5").Resize(UBound(aArr), UBound(aArr, 2)) = aArr
 End With
End Sub

[Bob1980]

Hi Mike,

Thanks for the assist. It looks like you're using a lot of methods that I'm not used to using. I'll also look into using your method.

Thanks all for the help.

bob1980