Run-time error 9 Subscript out of range help?

[ucsutah1]

My main goal is to compare column B to two workbooks and if anything in the columns doesn't match to put a 0 but leave the matches alone. I attached two examples.

thanks to FloLie for this code however whatever I do there is a subscript out of range in this part of the code

If compare1(i) = compare2(j) Then

I can't figure it out and I haven't heard anything from him as well.

[FONT=source_sans_proregular]https://mega.nz/#!O8kCFZJC!Y8Notb129OyMEHZtFrL6COrR0b8WSe1VL0B9IAKWb_8
[/FONT][FONT=source_sans_proregular]https://mega.nz/#!jpUSwQKa!RuZUAzuu3grq_ghb9JZ1eSIJRGZJKmJajF1-2cpFlZs[/FONT]

Sub test()    Dim wbSource As Workbook, wbTarget As Workbook
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim compare1 As Variant, compare2 As Variant, result As Variant


    Set wbSource = Workbooks("datafeed1.xlsx")
    Set wbTarget = Workbooks("result1.xlsx")
    Set wsSource = wbSource.Sheets("datafeed")
    Set wsTarget = wbTarget.Sheets("Sheet 1")


    compare1 = wsSource.Range("B2", wsSource.Range("B" & Rows.Count).End(xlUp))
    compare2 = wsTarget.Range("B2", wsTarget.Range("B" & Rows.Count).End(xlUp))


    ReDim result(LBound(compare1), UBound(compare1))


    For i = LBound(compare1) To UBound(compare1)
        found = False
        For j = LBound(compare2) To UBound(compare2)
            If compare1(i) = compare2(j) Then
                result(i) = 1
                found = True
                Exit For
            End If
        Next j
        If found = False Then
            result(i) = 0
        End If
    Next i
    wsSource.Range(Cells(2, 4), Cells(UBound(result) + 1, 4)) = Results


End Sub

 

[igold]

Have you tried to debug the code either by stepping through it with F8, or in Debug mode when it highlights the line in yellow, if you hover over the two parts, Excel will show an information box as to which "compare" has the subscript error.

I hope that makes sense.

 

[ucsutah1]

thanks igold,

yes that's exactly what i did, everything steps through the code fine until it hits the If compare1(i) = compare2(j) Then. The LBound = 1 and the UBound = 99 for Compare1 & Compare 1, The LBound=1 and UBountd = 99 for compare 2. Which works fine. But then the next line if compare1(i) = compare2(j) then, it says subscript out of range. I tried everything and nothing seems to fix it?

 

[igold]

Did you hover over those in debug mode so the information box pops up. It would seem that one of the two does not have the subscript you think it has...

Or perhaps they are two dimensional did you try something like

if compare1(i,1) = compare2(j,1) then

 

[wideboydixon]

This line:

            If compare1(i) = compare2(j) Then

I believe should be this:

            If compare1(i, 1) = compare2(j, 1) Then

But I also think this:

    ReDim result(LBound(compare1), UBound(compare1))

Should be this:

    ReDim result(LBound(compare1) To UBound(compare1))

WBD

 

[ucsutah1]

thanks igold helping with this.

I hover over compare1(i) = <subscript out of range>, I hover over compare2(j)= <subcript out of range>

Am I missing something? Is the code incorrect?

 

[iggy_]

Like igold and wideboydixon said you have a two dimensional range so you will need to update those to (i,1) and (j,1).
You will also have an issue with this statement because I don't see results defined:
wsSource.Range(Cells(2, 4), Cells(UBound(result) + 1, 4)) = Results

What is the goal of this macro? Would you like to change the values in the target sheet, or have output in a separate cell?

 

[ucsutah1]

that fixed those issues thank you.

Its just about working but now it has an error on the last bit of code just before end sub. The error is Run-time error '1004': Method 'Range' of object'_Worksheet' failed?

[COLOR=#333333]wsSource.Range(Cells(2, 4), Cells(UBound(result) + 1, 4)) = Results[/COLOR]

 

[ucsutah1]

change values in the target sheet

 

[wideboydixon]

wsSource.Range(wsSource.Cells(2, 4), wsSource.Cells(UBound(result) + 1, 4)) = result

WBD