From: Rick Rothstein on
Why are you MessagBox'ing the ActiveCell's address? Inside the For Each
loop, the loop variable (your 'x' variable in this case) references the cell
being looked at during each iteration of the loop... just use it... it is a
Range variable. I'm assuming you have Dim'med 'x' as a Range variable
although if you didn't, it will be a (slower) Variant and that should still

Dim x As Range
For Each x In Range("C1:C100")
If Not IsEmpty(x) Then
MsgBox x.Address
End If

Rick (MVP - Excel)

"Jason" <nospam(a)> wrote in message
> So as I find a cell that isn't empty, how do I actually get a hold of that
> cell location, i.e c2 c5 c10 ?
> For Each x In Range("c1:c100")
> If Not IsEmpty(x) Then
> Debug.Print "The value is " & x.Text
> MsgBox ActiveCell.Address
> End If
> Next x
> "Rick Rothstein" <rick.newsNO.SPAM(a)> wrote in message
> news:OakEerB0KHA.3572(a)TK2MSFTNGP02.phx.gbl...
>> It is usually helpful to tell us exactly what error message you are
>> getting and which line it is occurring on. With that said, I think your
>> problem is in the Debug.Print statement, namely, the variable "x" is
>> already a range, so you don't have to encase it in a call to Range. Try
>> that line like this...
>> Debug.Print "The value is " & x.Text
>> --
>> Rick (MVP - Excel)
>> "Jason" <nospam(a)> wrote in message
>> news:eophsjB0KHA.2552(a)TK2MSFTNGP04.phx.gbl...
>>> Hello
>>> I'm trying to get the cell value, if the cell has a value over a certain
>>> range, but when I run this, I keep getting a run time error. Any ideas?
>>> Can I not use the range funtion like this?
>>> Sub CellValue()
>>> Dim x
>>> For Each x In Range("c1:c100")
>>> If Not IsEmpty(x) Then
>>> Debug.Print "The value is " & Range(x).Text
>>> End If
>>> Next x
>>> End Sub