Prev: Arrow color with Autofilter (with Excel)
Next: Fiscal Year Week Number Calculation with a twist....
From: Jason on 30 Mar 2010 10:54
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


From: Rick Rothstein on 30 Mar 2010 11:08
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
>
From: Jason on 30 Mar 2010 11:48
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" <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
>>


From: Jason on 30 Mar 2010 11:54
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
>>


From: Dave Peterson on 30 Mar 2010 12:42
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" <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
> >>

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: Arrow color with Autofilter (with Excel)
Next: Fiscal Year Week Number Calculation with a twist....