auto move cursor

[Joanne]

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

 

[GB]

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.

 

[Chip Pearson]

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]

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

 

[GB]

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