Cell reference

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
I have a macro that I use for comparing columns in two different worksheets. The worksheets are identical in stucture ie there may be numbers in cells that differ but all rows and columns look the same. I want to check a certain column starting at the cell "b6". therefore i decalre at the top of the module:

option explicit
Dim tickerCell As Range
.....
I then want to do the comparison:

Private Sub compareRatings()

Dim i As Long
Set tickerCell = Range("b6")

i = i + 1
Do Until IsEmpty(tickerCell.Offset(i, 0))
If Sheets(i - 1).Range(tickerCell).Offset(i, 0) <> Sheets(i).Range(tickerCell).Offset(i, 0) Then
MsgBox "diff"
End If
i = i + 1
Loop

End Sub

there si something wrong here with the usage of the tickCell but I do not know how to write it. please help me! any help appreciated! thanks alot in advance!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You can't use IsEmpty in this way - try something like:

Code:
 Do Until tickerCell.Offset(i, 0).Value = ""

Richard
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
tickerCell is a Range object, not a reference address. Maybe try:

If Sheets(i - 1).Range(tickerCell.Address).Offset(i, 0) <> Sheets(i).Range(tickerCell.Address).Offset(i, 0) Then

When i exceeds the number of sheets you will get an error. You should loop around Worksheets.Count.
 

Forum statistics

Threads
1,140,923
Messages
5,703,178
Members
421,280
Latest member
Jaycee01

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