Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i use IsEmpty and IF this way? What's wrong here? | Excel Programming | |||
Do.....Loop until IsEmpty(....) | Excel Programming | |||
Help with IsEmpty | Excel Programming | |||
isempty | Excel Programming | |||
vba: isempty | Excel Programming |