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

exceldemon

New Member
Joined
Jun 30, 2010
Messages
38
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.

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


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

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,482
Members
414,144
Latest member
UDFNewbie

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
Top