IsEmpty
 

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!

IsEmpty
 

You say you are comparing just 2 sheets.
The objects Sheets(i - 1) and Sheets(i) will be different sheets in the
workbook everytime i is incremented. The should be changed to
Sheets("MyName1") and Sheets("MyName2")

Might also be worth specifying the sheet when assigning the range
variable i.e
Set tickerCell = Range("b6") changed to something like:
Set tickerCell = Sheets("MyName1").Range("b6")

Regards
Jason.



Arne Hegefors wrote:
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!

IsEmpty
 

Hi
If i = 1 then you would have Sheets(0), which does not exist. Try
i = 1
i = i + 1

at the top of your code.
regards
Paul

Arne Hegefors wrote:
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!

IsEmpty
 

Hi
Just spotted a couple of other things. The Range method is expecting a
cell address, not a range object. Since tickerCell is fixed and you
need it on more than one sheet I would skip setting it as a variable
Try this

Private Sub compareRatings()
Dim i As Long

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

End Sub

I don't know which "B6" you are checking IsEmpty, but I've guessed it
is the one on Sheets(i - 1).
just a note, but I tend to come across problems using IsEmpty. You
might try

Do Until Trim(Sheets(i - 1).Range("B6").Offset(i, 0).Value)=""

just incase there is a space in your cell rather than it being truly
empty.

regards
Paul

Arne Hegefors wrote:
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!