Run-Time Error on Cell Range Select

[Phil H]

I get a run-time error 1004 on the .Select line. Can someone suggest a fix?

Sub FindDate()
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
End Sub

 

[ker_01]

Phil-

My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.

What are your variable values just before the select statement?

Please forgive me if you already know how to find these, but if you don't
you can either set a watch on the values, set a breakpoint in code and then
mouse-over the variables to see their values, or you can put in a msgbox such
as the following right before the line that errors out:

msgbox actdate & chr(13) & rn & chr(13) & stdate
to provide the three critical values and include them in a follow-up post.

What I suspect is happening: As a general rule I'd suggest avoiding VB
commands as variable names. Instead of
offset = actdate – stdate
try using
MyOffset = ...
or something else you personalize; using VBA keywords can have unintended
and sometimes undesirable effects. In this case, on your select statement, I
suspect Excel thinks you are using the VBA command "offset" instead of your
own variable name, in which case Excel thinks your select statement (or the
offset piece of it, anyway) is missing parameters in your syntax
[offset(x,y)].

HTH,
Keith

 

[Phil H]

Hi Kieth, thanks for your reply. I substituted "MyOffset" everywhere
"offset" appeared but still get the R-T 1004...

Phil-

My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.

What are your variable values just before the select statement?

Please forgive me if you already know how to find these, but if you don't
you can either set a watch on the values, set a breakpoint in code and then
mouse-over the variables to see their values, or you can put in a msgbox such
as the following right before the line that errors out:

msgbox actdate & chr(13) & rn & chr(13) & stdate
to provide the three critical values and include them in a follow-up post.

What I suspect is happening: As a general rule I'd suggest avoiding VB
commands as variable names. Instead of
offset = actdate – stdate
try using
MyOffset = ...
or something else you personalize; using VBA keywords can have unintended
and sometimes undesirable effects. In this case, on your select statement, I
suspect Excel thinks you are using the VBA command "offset" instead of your
own variable name, in which case Excel thinks your select statement (or the
offset piece of it, anyway) is missing parameters in your syntax
[offset(x,y)].

HTH,
Keith

I get a run-time error 1004 on the .Select line. Can someone suggest a fix?

Sub FindDate()
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
End Sub

 

[Per Jessen]

Hi

Nothing wrong with your code. What are the values in the variables, rn &
MyOffset? (When your macro stop with the error, move the mouse pointer over
the variable to see the value)

I think that the code point to cells outside the sheet!

Regards,
Per

Hi Kieth, thanks for your reply. I substituted "MyOffset" everywhere
"offset" appeared but still get the R-T 1004...

Phil-

My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.

What are your variable values just before the select statement?

Please forgive me if you already know how to find these, but if you don't
you can either set a watch on the values, set a breakpoint in code and
then
mouse-over the variables to see their values, or you can put in a msgbox
such
as the following right before the line that errors out:

msgbox actdate & chr(13) & rn & chr(13) & stdate
to provide the three critical values and include them in a follow-up
post.

What I suspect is happening: As a general rule I'd suggest avoiding VB
commands as variable names. Instead of
offset = actdate – stdate
try using
MyOffset = ...
or something else you personalize; using VBA keywords can have unintended
and sometimes undesirable effects. In this case, on your select
statement, I
suspect Excel thinks you are using the VBA command "offset" instead of
your
own variable name, in which case Excel thinks your select statement (or
the
offset piece of it, anyway) is missing parameters in your syntax
[offset(x,y)].

HTH,
Keith

I get a run-time error 1004 on the .Select line. Can someone suggest a
fix?

Sub FindDate()
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
End Sub

 

[Ryan H]

Your offset or MyOffset variable is probaly a negative number less than -2.
In other words it might be -3, -4, -5, and so on. If your MyOffset = -5 and
rn = 4 Excel will throw an error on the line you are indicating, because it
is trying to select a cell that doesn't exist. In the example:

Range(Cells(rn + 6, 3 + MyOffset), Cells(rn + 7, 3 + MyOffset)).Select

Range(Cells(10,-2), Cells(11, -2)).Select

Obviously, Col. -2 doesn't exist in Excel, the columns start at Col. 1 =
Col. A, Col. 2 = Col. B, and so on.

I would suggest reworking your code to ensure that MyOffset isn't less than
-2. Or just tell us what you are wanting to achieve with this macro in
detail and we could rework it for you. Hope this helps! If so, let me know,
click "YES" below.
--
Cheers,
Ryan

Phil-

My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.

What are your variable values just before the select statement?

Please forgive me if you already know how to find these, but if you don't
you can either set a watch on the values, set a breakpoint in code and then
mouse-over the variables to see their values, or you can put in a msgbox such
as the following right before the line that errors out:

msgbox actdate & chr(13) & rn & chr(13) & stdate
to provide the three critical values and include them in a follow-up post.

What I suspect is happening: As a general rule I'd suggest avoiding VB
commands as variable names. Instead of
offset = actdate – stdate
try using
MyOffset = ...
or something else you personalize; using VBA keywords can have unintended
and sometimes undesirable effects. In this case, on your select statement, I
suspect Excel thinks you are using the VBA command "offset" instead of your
own variable name, in which case Excel thinks your select statement (or the
offset piece of it, anyway) is missing parameters in your syntax
[offset(x,y)].

HTH,
Keith

I get a run-time error 1004 on the .Select line. Can someone suggest a fix?

Sub FindDate()
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
End Sub

 

[Ryan H]

Your offset or MyOffset variable is probaly a negative number less than -2.
In other words it might be -3, -4, -5, and so on. If your MyOffset = -5 and
rn = 4 Excel will throw an error on the line you are indicating, because it
is trying to select a cell that doesn't exist. In the example:

Range(Cells(rn + 6, 3 + MyOffset), Cells(rn + 7, 3 + MyOffset)).Select

Range(Cells(10,-2), Cells(11, -2)).Select

Obviously, Col. -2 doesn't exist in Excel, the columns start at Col. 1 =
Col. A, Col. 2 = Col. B, and so on.

I would suggest reworking your code to ensure that MyOffset isn't less than
-2. Or just tell us what you are wanting to achieve with this macro in
detail and we could rework it for you. Hope this helps! If so, let me know,
click "YES" below.

--
Cheers,
Ryan

Hi Kieth, thanks for your reply. I substituted "MyOffset" everywhere
"offset" appeared but still get the R-T 1004...

Phil-

My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.

What are your variable values just before the select statement?

Please forgive me if you already know how to find these, but if you don't
you can either set a watch on the values, set a breakpoint in code and then
mouse-over the variables to see their values, or you can put in a msgbox such
as the following right before the line that errors out:

msgbox actdate & chr(13) & rn & chr(13) & stdate
to provide the three critical values and include them in a follow-up post.

What I suspect is happening: As a general rule I'd suggest avoiding VB
commands as variable names. Instead of
offset = actdate – stdate
try using
MyOffset = ...
or something else you personalize; using VBA keywords can have unintended
and sometimes undesirable effects. In this case, on your select statement, I
suspect Excel thinks you are using the VBA command "offset" instead of your
own variable name, in which case Excel thinks your select statement (or the
offset piece of it, anyway) is missing parameters in your syntax
[offset(x,y)].

HTH,
Keith

I get a run-time error 1004 on the .Select line. Can someone suggest a fix?

Sub FindDate()
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
End Sub