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

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" wrote in message
...
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

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


"Rick Rothstein" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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



Jason wrote:

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" wrote in message
...
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" wrote in message
...
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


--

Dave Peterson

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" 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" wrote in message
...
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" wrote in message
...
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