Prev: Arrow color with Autofilter (with Excel)
Next: Fiscal Year Week Number Calculation with a twist....
From: Rick Rothstein on 30 Mar 2010 12:43
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
work.

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

--
Rick (MVP - Excel)



"Jason" <nospam(a)spam.com> wrote in message
news:ONdrJFC0KHA.3572(a)TK2MSFTNGP02.phx.gbl...
> 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)NO.SPAMverizon.net> 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)spam.com> 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
>>>
>
>
First  |  Prev  | 
Pages: 1 2
Prev: Arrow color with Autofilter (with Excel)
Next: Fiscal Year Week Number Calculation with a twist....