Nested loop to match cell text for two columns in different sheets

**lottidotti** · 12-07-2012, 08:33 AM

Hello,
I am trying to get this nested loop to work and I keep getting errors.
Can someone please help me fix my code?

I have two sheets with column ranges that I'm comparing text to see if it matches:
Find Stock Symbols
Stock & Symbol Data

For x = 5 To LastRowBofF
    For y = 2 To LastRowBofS
    If Worksheets("Find Stock Symbols").Range("B" & x).Value = Worksheets("Stock & Symbol Data").Range("B" & y).Value Then
Worksheets("Find Stock Symbols").Range("C" & x).Value = Worksheets("Stock & Symbol Data").Range("A" & y).Value
    Else
        Next y
    End If
    y = 2
Next x

Thanks for any help!

**TMS** · 12-07-2012, 08:47 AM

I suggest you post a sample workbook.

I suspect it would be easier to do this with a formula.

It looks as though it would be an INDEX/MATCH as you want to return a value from the column before the one being searched.

Untested, but try:

=IFERROR(INDEX('Stock & Symbol Data'!$A:$A,MATCH('Find Stock Symbols'!$B2, 'Stock & Symbol Data'!$B:$B, FALSE)),0)

Regards, TMS

**nested.if1@gmail.com** · 12-07-2012, 08:53 AM

Hi,

I am not very good at this, but I looks like the loop is incorrect.
"Next y" is before "End IF". Check by replacing "Else" With "End If".

For x = 5 To LastRowBofF
    For y = 2 To LastRowBofS
    If Worksheets("Find Stock Symbols").Range("B" & x).Value = Worksheets("Stock & Symbol Data").Range("B" & y).Value Then
Worksheets("Find Stock Symbols").Range("C" & x).Value = Worksheets("Stock & Symbol Data").Range("A" & y).Value
    End If
        Next y
    
    y = 2
Next x

**lottidotti** · 12-07-2012, 10:48 AM

You hit the nail on the head! Switching the "Next y" and "End If" and replacing the "Else" did the trick.
Thanks so much for everyone's feedback.

Originally Posted by nested.if1@gmail.com

Hi,

I am not very good at this, but I looks like the loop is incorrect.
"Next y" is before "End IF". Check by replacing "Else" With "End If"....

For x = 5 To LastRowBofF
    For y = 2 To LastRowBofS
    If Worksheets("Find Stock Symbols").Range("B" & x).Value = Worksheets("Stock & Symbol Data").Range("B" & y).Value Then
Worksheets("Find Stock Symbols").Range("C" & x).Value = Worksheets("Stock & Symbol Data").Range("A" & y).Value
    End If
        Next y
    
    y = 2
Next x

**TMS** · 12-07-2012, 09:06 AM

Try:

' just to make it easier to read
Dim wsFSS As Worksheet
Dim wsSSD As Worksheet
Set wsFSS = Worksheets("Find Stock Symbols")
Set wsSSD = Worksheets("Stock & Symbol Data")

For x = 5 To LastRowBofF
    For y = 2 To LastRowBofS
        If wsFSS.Range("B" & x).Value = _
            wsSSD.Range("B" & y).Value _
        Then
            wsFSS.Range("C" & x).Value = _
                wsSSD.Range("A" & y).Value
        End If
    Next 'y
Next 'x