compare two cells

[SangelNet]

seeking help on how to compare cell in two columns and get a message
if one is empty.

i have column T and U and would like to get a message if cell t1 has
something and cell u1 has nothing. if cell in column t have nothing
just exit the sub. if T has something and u has something just exit
the sub. just message when t has something and u does not.

i tries looking up something and nothing fits that need.

tried to cook up my own, but does not work. here is what i got

Sub compare()

Dim T As Range 'column T
Dim U As Range 'column U
Dim c As Range

Set T = Range("T")
Set U = Range("U")

For Each c In T

If T.Cells.Value = "" Then 'If T is empty the leave
Exit Sub
Else

If T.Cells = 1 And U.Cells = 1 Then '1 as in something 0
as in nothing
Exit Sub
Else
If T.Cells = 1 And U.Cells = 0 Then '1 as in something 0
as in nothing
MsgBox ("my message here")
End If
End If
End If

Next c

End Sub

any help ? thnx

 

[Rick Rothstein]

There are a few things wrong with your code (I think you knew that
though<g>). First, Range("T") is not a valid range... use either
Range("T:T") or Columns("T"). Inside the For Each loop, your 'c' variable is
set to a cell in the T range, so you should be using c, not T.Cells, to
reference the cell. For example, your first test should be this..

If c.Value = "" Then

instead of this...

If T.Cells.Value = "" Then

You are also doing more testing than you need to in two different ways.
First, your code is testing every cell in Column T (all 65000+ of them in
XL2003 or earlier, more than a million of them in XL2007)... I would only
test down to the last filled in cell in Column T. Second, you are only
interested in seeing if one particular situation exists in order to popup
your MessageBox (you take no action for the other tests you are doing), but
you are performing more testing than that... only perform the one test you
are interested in. Here is how I would write your code...

Sub Compare()
Dim C As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "T").End(xlUp).Row
For Each C In Range("T1:T" & LastRow)
If C.Value <> "" And C.Offset(0, 1).Value = "" Then
MsgBox "Your message goes here"
End If
Next
End Sub

 

[JLGWhiz]

This code will walk the populated cells of Column T and check the content of
the adjacent cell in column U. If there is no value in column U the message
box will appear and display the address of the blank cell. Put this in the
standard code module1.

Sub Due()
lr = ActiveSheet.Cells(Rows.Count, 20).End(xlUp).Row
Set srcRng = ActiveSheet.Range("T2:T" & lr)
For Each c In srcRng
If c.Offset(0, 1).Value = "" Then
MsgBox "No Value in " & c.Offset(0, 1).Address
End If
Next
End Sub

 

[Rick Rothstein]

You are not testing c.Value meaning you will pop a message when both c and
the offset are empty which the OP didn't want to have happen (unless, of
course, there are no blank rows internal to the data).

 

[JLGWhiz]

Yep, missed that. Thanks Rick.

If c.Value > "" And c.Offset(0, 1).Value = "" Then
MsgBox "No Value in " & c.Offset(0, 1).Address
End If

 

[SangelNet]

Yep, missed that.  Thanks Rick.

If c.Value > "" And c.Offset(0, 1).Value = "" Then
       MsgBox "No Value in " & c.Offset(0, 1).Address
End If

Thnx a lot to both . Worked Just Fine!!!