Run-time error 9 Subscript out of range help?

ucsutah1

Board Regular
Joined
Jan 17, 2011
Messages
56
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
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]


Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
FYI, If you do not want to use Application.Transpose, you could have instead Redim result as a two dimensional array as follows:

Code:
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), [COLOR=#ff0000]1 To 1[/COLOR])


    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([COLOR=#ff0000]i, 1[/COLOR]) = 1
                found = True
                Exit For
            End If
        Next j
        If found = False Then
            result([COLOR=#ff0000]i, 1[/COLOR]) = 0
        End If
    Next i
    wsSource.Range("D2").Resize(UBound(result)) = result


End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top