Get cell value

[Jason]

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

 

[Rick Rothstein]

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

 

[Jason]

The error I was getting was Run-time error '1004':
Application-defined or object-defined error


Made the change, you recommned, and that resolved my issue, thanks

 

[Jason]

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

 

[Dave Peterson]

Dim x as range

For Each x In Range("c1:c100").cells
If Not IsEmpty(x) Then
Debug.Print "The value is " & x.Text
MsgBox x.Address
exit for 'if you want to stop after the first non-empty cell.
End If
Next x

 

[Rick Rothstein]

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