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]

Errant post...

 

[ucsutah1]

That was it thank you for your help it was much appreciated.

 

[igold]

Great. Glad you got it figured out. We were all happy to pitch in. Thanks for the feedback

 

[ucsutah1]

Hi, I just got back from my trip and ran this code and I thought it worked fine before but there is an issue. When it matches it only matches the first row and then it copies to the end the match on the first row. For example lets say column b on result is 123456 and column b on datafeed is 234678. It should result to 0, 1, 1, 1, 0, 1, but instead it goes 0, 0, 0, 0, 0, 0 till the end of the column. I don't think its looping properly or something else is going wrong? thanks

 

[igold]

Perhaps you could post your code as it stands now. It will make it easier for everyone if we are all looking at the same thing...

 

[ucsutah1]

oh ya, I am sorry I wasn't even thinking. Here it is.

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) To UBound(compare1))

    For i = LBound(compare1) To UBound(compare1)
        found = False
        For j = LBound(compare2) To UBound(compare1)
             If compare1(i, 1) = compare2(j, 1) 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(wsSource.Cells(2, 4), wsSource.Cells(UBound(result) + 1, 4)) = result

End Sub

 

[igold]

Before I dive in, I know that you are not using "Option Explicit" (you should, btw), might it just be a typo as indicated below in RED. "Sheet 1" as opposed to "Sheet1".

oh ya, I am sorry I wasn't even thinking. Here it is.

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")
    [COLOR=#ff0000]Set wsTarget = wbTarget.Sheets("Sheet 1")[/COLOR]

    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) To UBound(compare1))

    For i = LBound(compare1) To UBound(compare1)
        found = False
        For j = LBound(compare2) To UBound(compare1)
             If compare1(i, 1) = compare2(j, 1) 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(wsSource.Cells(2, 4), wsSource.Cells(UBound(result) + 1, 4)) = result

End Sub

 

[igold]

Does this work:

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
    Dim found As Boolean
    Dim i As Long, j As Long
    
    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) To UBound(compare1))


    For i = LBound(compare1) To UBound(compare1)
        found = False
        For j = LBound(compare2) To UBound(compare1)
             If compare1(i, 1) = compare2(j, 1) 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(wsSource.Cells(2, 4), wsSource.Cells(UBound(result) + 1, 4)) [COLOR=#ff0000]= Application.Transpose(result)[/COLOR]


End Sub

I hope this helps.

 

[ucsutah1]

Yes, you got it, thank you, thank you. Yes, that was a typo, thanks for pointing that out. So all it was = Application.Transpose(result)

I really appreciate it

 

[igold]

I am glad it is working. One caveat, I think there is a limit (about 16k rows) that Application.Transpose can handle. Thanks again for the feedback.

Also do yourself a favor and use Option Explicit, it will one day save you a lot of time...