WinXP MSOffice 2003

The following procedure is giving me an 'object or with block variable
not set' error. I am trying to skip one cell in a row based on the value
in another cell.
Do I have to put the cell address in there, such as if 'C15".value = 65
then cell.offset(0,2).select (I am hoping this is the correct syntax to
move my cursor to 'E15'). Or do I need another Dim statement. I am using
some code given to me by GTVT06 from this group as a pattern and trying
to get this procedure to work on my own - but, alas, it is not to be.
I need help, please!!

Public Sub SkipLaborRate()
Dim cell As Range
Dim acd As Variant
acd = ActiveCell.Address
If cell.Value = 65 Then
cell.Offset(0, 2).Select
End If
End Sub

Joanne

Try this, it seems to work.
Public Sub SkipLaborRate()
Dim cell As Range
' There was no need to have the acd variant, and the method of
assignment didn't force cell to be anything measurable.
Set cell = ActiveCell 'Assigns the cell to be equal to the ActiveCell
If cell.Value = 65 Then
cell.Offset(0, 2).Select
End If
End Sub

If you need acd to remain in the procedure, then set acd to be the address
of the active cell like you did, then
set cell = range (acd)

and continue as above.
"Joanne" wrote:

> WinXP MSOffice 2003
>
> The following procedure is giving me an 'object or with block variable
> not set' error. I am trying to skip one cell in a row based on the value
> in another cell.
> Do I have to put the cell address in there, such as if 'C15".value = 65
> then cell.offset(0,2).select (I am hoping this is the correct syntax to
> move my cursor to 'E15'). Or do I need another Dim statement. I am using
> some code given to me by GTVT06 from this group as a pattern and trying
> to get this procedure to work on my own - but, alas, it is not to be.
> I need help, please!!
>
> Public Sub SkipLaborRate()
> Dim cell As Range
> Dim acd As Variant
> acd = ActiveCell.Address
> If cell.Value = 65 Then
> cell.Offset(0, 2).Select
> End If
> End Sub
>
> Joanne
>

Joanne,

You have declared the variable "cell" and you attempt to use it in the line
"If cell.Value = 65", but you have not assigned the variable "cell" to refer
to any specific cell. In your code, "cell" can be assign to refer to any
cell, but if you don't do assignment with a "Set" statement, "cell" has the
special value "Nothing" which means that it doesn't refer to any cell at
all. You can remedy this with code like the following:

Dim cell As Range
Set cell = ActiveCell '<<< makes "cell" refer to ActiveCell
If cell..Value = 65 Then
' rest of the code

Or, you can omit the "cell" value entirely:

Public Sub SkipLaborRate()
If ActiveCell.Value = 65 Then
ActiveCell.Offset(0, 2).Select
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

"Joanne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> WinXP MSOffice 2003
>
> The following procedure is giving me an 'object or with block variable
> not set' error. I am trying to skip one cell in a row based on the value
> in another cell.
> Do I have to put the cell address in there, such as if 'C15".value = 65
> then cell.offset(0,2).select (I am hoping this is the correct syntax to
> move my cursor to 'E15'). Or do I need another Dim statement. I am using
> some code given to me by GTVT06 from this group as a pattern and trying
> to get this procedure to work on my own - but, alas, it is not to be.
> I need help, please!!
>
> Public Sub SkipLaborRate()
> Dim cell As Range
> Dim acd As Variant
> acd = ActiveCell.Address
> If cell.Value = 65 Then
> cell.Offset(0, 2).Select
> End If
> End Sub
>
> Joanne

Thank you for the help guys - I've learned something new and my proc is
not usable
Joanne

Chip Pearson wrote:

> Joanne,
>
> You have declared the variable "cell" and you attempt to use it in the line
> "If cell.Value = 65", but you have not assigned the variable "cell" to refer
> to any specific cell. In your code, "cell" can be assign to refer to any
> cell, but if you don't do assignment with a "Set" statement, "cell" has the
> special value "Nothing" which means that it doesn't refer to any cell at
> all. You can remedy this with code like the following:
>
> Dim cell As Range
> Set cell = ActiveCell '<<< makes "cell" refer to ActiveCell
> If cell..Value = 65 Then
> ' rest of the code
>
> Or, you can omit the "cell" value entirely:
>
> Public Sub SkipLaborRate()
> If ActiveCell.Value = 65 Then
> ActiveCell.Offset(0, 2).Select
> End If
> End Sub
>
>
>
>

I wouldn't say that your procedure can't be used, its just that it couldn't
be used as written. We have been providing assistance based on knowing that
the value is "65", you can use any mathematical comparison or review of the
data that will meet your skip boundary condition. If it is desired to skip
the cell to the right if it is less than the current cell then the if
statement could be as below:

if activecell.offset(0,1).value < activecell.value then
activecell.offset(0,2).select
end if

GB

"Joanne" wrote:

> Thank you for the help guys - I've learned something new and my proc is
> not usable
> Joanne
>
> Chip Pearson wrote:
>
> > Joanne,
> >
> > You have declared the variable "cell" and you attempt to use it in the line
> > "If cell.Value = 65", but you have not assigned the variable "cell" to refer
> > to any specific cell. In your code, "cell" can be assign to refer to any
> > cell, but if you don't do assignment with a "Set" statement, "cell" has the
> > special value "Nothing" which means that it doesn't refer to any cell at
> > all. You can remedy this with code like the following:
> >
> > Dim cell As Range
> > Set cell = ActiveCell '<<< makes "cell" refer to ActiveCell
> > If cell..Value = 65 Then
> > ' rest of the code
> >
> > Or, you can omit the "cell" value entirely:
> >
> > Public Sub SkipLaborRate()
> > If ActiveCell.Value = 65 Then
> > ActiveCell.Offset(0, 2).Select
> > End If
> > End Sub
> >
> >
> >
> >
>
>