Comparing columns, error Method 'Range' of object_Global' failed

[exceldemon]

I am trying to create a macro that compares a list of data in column E with data in column B, starting at row 11.

Basically, i want to go through each value in column E and check if it exist anywhere in column B, if it does, put a Y in the corresponding cell in column F. It doe not have to be in the same row. See example below. 123 and 423 are in both columns.


Ex.

Column B          Column E      Column F
123                    3222
323                    123            Y
2342                  3323 
423                    423            Y

I am getting a error

Method 'Range' of object_Global' failed
on line
Range("E" & currRow).Select


I cant figure out what the issue is.

Any ideas?

Sub Compare()
Dim currFILE, curlist As String
Dim sourceFolder As String
Dim destFolder As String
Dim currRow, currow2 As String
Dim filespec As String
Dim fileEXT As String

stillreading = True
currow = 11

While stillreading
    Range("E" & currRow).Select
    currlist = ActiveCell.FormulaR1C1

    If currlist = "" Then
        stillreading = False
    Else
        stillreading2 = True
        While stillreading2
        currow2 = 11
            Range("B" & currow2).Select
            currFILE = ActiveCell.FormulaR1C1
            If currFILE = "" Then
                stillreading2 = False
            Else
                If InStr(1, currFILE, currlist, 1) Then
                    Range("F" & currow2).Value = "Y"
                    stillreading2 = False
                End If
            End If
            currow2 = currow2 + 1
        Wend
    
    currRow = currRow + 1
   End If
Wend

End Sub

 

[pgc01]

Hi

This is one of those avoidable errors from which you learn nothing, just waste time.

You have the variables declared, which is good programming, but you forgot a fundamental statement at the beginning of the module:

Option Explicit

This statement makes sure that all the variables you use are indeed declared and it's a fundamental help to programming and it detects, for ex. as in this case, typing errors.

You should always write it as the first statement of every module, or, simpler, turn on the option "Require Variable Declaration", that inserts that statement in every module you create.

In this case I had to do nothing. Just pasted your code, tried to run it and immediately excel told me that the variable currow does not exist. In fact the variable you declared had 3 r's, currRow.

You just have to correct the typo.