Run-time error '1004': Application-defined or object-defined error

[s***@gmail.com]

I have the following code and I keep getting an error at line "Set timestamp = Target.Offset(0, -3)". What seems to be the problem. What I would like to do is whenever cells in column C is modified, corresponding column A will get timestamped.

TIA,
kray

---
Private Sub Worksheet_Change(ByVal Target As Range)

'MsgBox Target.Address
Dim timestamp As Range


Set timestamp = Target.Offset(0, -3)
'MsgBox timeStamp.Address

If timestamp.Value = "" Then
MsgBox "Nothing in there"
timestamp.Value = Now
Else
MsgBox timestamp
End If

End Sub

[GS]

Post by s***@gmail.com
I have the following code and I keep getting an error at line "Set timestamp
= Target.Offset(0, -3)". What seems to be the problem. What I would like to
do is whenever cells in column C is modified, corresponding column A will get
timestamped.
TIA,
kray
---
Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox Target.Address
Dim timestamp As Range
Set timestamp = Target.Offset(0, -3)
'MsgBox timeStamp.Address
If timestamp.Value = "" Then
MsgBox "Nothing in there"
timestamp.Value = Now
Else
MsgBox timestamp
End If
End Sub

Uh.., Column C minus 3 falls off the sheet since column A is -2!

[s***@gmail.com]

> I have the following code and I keep getting an error at line "Set timestamp
> = Target.Offset(0, -3)". What seems to be the problem. What I would like to
> do is whenever cells in column C is modified, corresponding column A will get
> timestamped.
>
> TIA,
> kray
>
> ---
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> 'MsgBox Target.Address
> Dim timestamp As Range
>
>
> Set timestamp = Target.Offset(0, -3)
> 'MsgBox timeStamp.Address
>
> If timestamp.Value = "" Then
> MsgBox "Nothing in there"
> timestamp.Value = Now
> Else
> MsgBox timestamp
> End If
>
> End Sub
Uh.., Column C minus 3 falls off the sheet since column A is -2!
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I noticed that when I sent it. Even if I change column 4, it errors out.

[GS]

Actually, you're asking in the wrong forum. This is a classic VB forum,
NOT an Excel programming forum...

(ie: microsoft.public.excel.programming)

..where you should provide a full explanation of what you're trying to
accomplish.

<Otherwise>
I don't know what you mean by "when I sent it", because that code fires
as an event that occurs whenever the user makes changes (ie: edits) to
the cells on the worksheet.

I don't understand the syntax you're using here as it's NOT proper VBA.
For example...

&#39;MsgBox timeStamp.Address

..what is "&#39;" in the above line of code? I doubt this compiled as
typed and so surely would throw an error when executed. I presume you
are not using 'Option Explicit' at the top of your code windows. It
also looks like you did a copy/paste of this code from a webpage.

Try this rewrite...

Private Sub Worksheet_Change(ByVal Target As Range)

'Bail out if not the correct column (edit to suit)
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub

'MsgBox Target.Address

With Target.Offset(0, -2) '//store timestamp 2 columns to the left
If .Value = "" Then MsgBox "Nothing in there": .Value = Now
'MsgBox .Value
End With

End Sub

[Auric__]

Post by GS
For example...
&#39;MsgBox timeStamp.Address
..what is "&#39;" in the above line of code?

HTML-ism, courtesy of Google Gropes. &#39; -> '

Check the original post; it shows up correctly there.

[GS]

Post by Auric__

Post by GS
For example...
&#39;MsgBox timeStamp.Address
..what is "&#39;" in the above line of code?

HTML-ism, courtesy of Google Gropes. &#39; -> '
Check the original post; it shows up correctly there.

Thanks, Auric! Yeah, that's where I copied my example lines from. I
just wasn't sure why the code changed since the OP.<g>

Regardless, the object ref in the Offset function can't be 'off the
worksheet', and so the error is raised since there is no column 3 cols
to the left of ColC.

I haven't tried this but I expect the same error would result if we
were trying to set an object ref to a cell outside a grid. I do know it
happens with fpSpread.ocx and so is why I'm making the comparison.

[Auric__]

Post by GS

Post by Auric__

Post by GS
For example...
&#39;MsgBox timeStamp.Address
..what is "&#39;" in the above line of code?

HTML-ism, courtesy of Google Gropes. &#39; -> '
Check the original post; it shows up correctly there.

Thanks, Auric! Yeah, that's where I copied my example lines from. I
just wasn't sure why the code changed since the OP.<g>
Regardless, the object ref in the Offset function can't be 'off the
worksheet', and so the error is raised since there is no column 3 cols
to the left of ColC.
I haven't tried this but I expect the same error would result if we
were trying to set an object ref to a cell outside a grid. I do know it
happens with fpSpread.ocx and so is why I'm making the comparison.

The problem I have with the code you posted is that it fires if any cell in
the changed range is in column C. Try copying a selection containing more
than 1 column to column B -- you'll get "Run-time error '1004': Application-
defined or object-defined error".

What I would do would be more like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If cell.Column = 3 Then
With Cells(cell.Row, 1)
If .Value = "" Then
MsgBox "Nothing in there"
.Value = Now
Else
MsgBox .Value
End If
End With
End If
Next
End Sub

Also, I agree with your pointing the OP to the Excel group.

[GS]

Post by Auric__

Post by GS

Post by Auric__

Post by GS
For example...
&#39;MsgBox timeStamp.Address
..what is "&#39;" in the above line of code?

HTML-ism, courtesy of Google Gropes. &#39; -> '
Check the original post; it shows up correctly there.

Thanks, Auric! Yeah, that's where I copied my example lines from. I
just wasn't sure why the code changed since the OP.<g>
Regardless, the object ref in the Offset function can't be 'off the
worksheet', and so the error is raised since there is no column 3 cols
to the left of ColC.
I haven't tried this but I expect the same error would result if we
were trying to set an object ref to a cell outside a grid. I do know it
happens with fpSpread.ocx and so is why I'm making the comparison.

The problem I have with the code you posted is that it fires if any cell in
the changed range is in column C. Try copying a selection containing more
than 1 column to column B -- you'll get "Run-time error '1004': Application-
defined or object-defined error".

I agree! ..good point. The problem as I see it is that this is
inefficient at best because the change should be limited to a specific
cell used to trigger the code. In this, perhaps, the ActiveCell
regardless of a multiple selection.

While your suggestion below will work fine as intended, it's never<IMO>
efficient to iterate and act on a selection cell-by-cell. Obviously the
OP has criteria that needs to be better defined!!!

Post by Auric__
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If cell.Column = 3 Then
With Cells(cell.Row, 1)
If .Value = "" Then
MsgBox "Nothing in there"
.Value = Now
Else
MsgBox .Value
End If
End With
End If
Next
End Sub
Also, I agree with your pointing the OP to the Excel group.

[Auric__]

[snip]

Post by GS

Post by Auric__
The problem I have with the code you posted is that it fires if any
cell in the changed range is in column C. Try copying a selection
containing more than 1 column to column B -- you'll get "Run-time error
'1004': Application- defined or object-defined error".

I agree! ..good point. The problem as I see it is that this is
inefficient at best because the change should be limited to a specific
cell used to trigger the code. In this, perhaps, the ActiveCell
regardless of a multiple selection.
While your suggestion below will work fine as intended, it's never<IMO>
efficient to iterate and act on a selection cell-by-cell. Obviously the
OP has criteria that needs to be better defined!!!

Of course it's inefficient -- but that's how most of my code is, so I'm
pretty used to it. ;-)

A different option, one that still operates using the cells (as opposed to
dumping them to an array or whatever) would be...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Range, cell As Range
For Each col In Target.Columns
If col.Column = 3 Then
For Each cell In col.Cells
With Cells(cell.Row, 1)
If .Value = "" Then
MsgBox "Nothing in there"
.Value = Now
Else
MsgBox .Value
End If
End With
Next
Exit For
ElseIf col.Column > 3 Then
Exit For
End If
Next
End Sub

This version only looks at the individual cells in the appropriate column,
improving the efficiency somewhat, and quickly exits if it starts *after* C.

[GS]

Post by Auric__

It happens that Auric__ formulated : [snip]

Post by Auric__
The problem I have with the code you posted is that it fires if any
cell in the changed range is in column C. Try copying a selection
containing more than 1 column to column B -- you'll get "Run-time error
'1004': Application- defined or object-defined error".

I agree! ..good point. The problem as I see it is that this is
inefficient at best because the change should be limited to a specific
cell used to trigger the code. In this, perhaps, the ActiveCell
regardless of a multiple selection.
While your suggestion below will work fine as intended, it's never<IMO>
efficient to iterate and act on a selection cell-by-cell. Obviously the
OP has criteria that needs to be better defined!!!

Of course it's inefficient -- but that's how most of my code is, so I'm
pretty used to it. ;-)
A different option, one that still operates using the cells (as opposed to
dumping them to an array or whatever) would be...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Range, cell As Range
For Each col In Target.Columns
If col.Column = 3 Then
For Each cell In col.Cells
With Cells(cell.Row, 1)
If .Value = "" Then
MsgBox "Nothing in there"
.Value = Now
Else
MsgBox .Value
End If
End With
Next
Exit For
ElseIf col.Column > 3 Then
Exit For
End If
Next
End Sub
This version only looks at the individual cells in the appropriate column,
improving the efficiency somewhat, and quickly exits if it starts *after* C.

Regardless of efficiency, your suggestions do get the job done. It
would be nice, though, if we knew more about what the goal is to be
accomplished so it can be addressed more directly (as opposed to
eventually by attrition of looping every cell).<g>

[s***@gmail.com]

&gt;&gt;
&gt;&gt;&gt; For example...
&gt;&gt;&gt;
&gt;&gt;&gt; &amp;#39;MsgBox timeStamp.Address
&gt;&gt;&gt;
&gt;&gt;&gt; ..what is &quot;&amp;#39;&quot; in the above line of code?
&gt;&gt;
&gt;&gt; HTML-ism, courtesy of Google Gropes. &amp;#39; -&gt; &#39;
&gt;&gt;
&gt;&gt; Check the original post; it shows up correctly there.
&gt;
&gt; Thanks, Auric! Yeah, that&#39;s where I copied my example lines from. I
&gt; just wasn&#39;t sure why the code changed since the OP.&lt;g&gt;
&gt;
&gt; Regardless, the object ref in the Offset function can&#39;t be &#39;off the
&gt; worksheet&#39;, and so the error is raised since there is no column 3 cols
&gt; to the left of ColC.
&gt;
&gt; I haven&#39;t tried this but I expect the same error would result if we
&gt; were trying to set an object ref to a cell outside a grid. I do know it
&gt; happens with fpSpread.ocx and so is why I&#39;m making the comparison.
The problem I have with the code you posted is that it fires if any cell in
the changed range is in column C. Try copying a selection containing more
than 1 column to column B -- you&#39;ll get &quot;Run-time error &#39;1004&#39;: Application-
defined or object-defined error&quot;.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If cell.Column = 3 Then
With Cells(cell.Row, 1)
If .Value = &quot;&quot; Then
MsgBox &quot;Nothing in there&quot;
.Value = Now
Else
MsgBox .Value
End If
End With
End If
Next
End Sub
Also, I agree with your pointing the OP to the Excel group.
--
No, I never read the documentation -- why do you ask?

Thanks the above code works. I am just wondering about the error I'm getting but that will be for another day. Better read something about this topic.

[Auric__]

[snip]

Post by s***@gmail.com

Post by s***@gmail.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If cell.Column = 3 Then
With Cells(cell.Row, 1)
If .Value = "" Then
MsgBox "Nothing in there"
.Value = Now
Else
MsgBox .Value
End If
End With
End If
Next
End Sub
Also, I agree with your pointing the OP to the Excel group.

Thanks the above code works. I am just wondering about the error I'm
getting but that will be for another day. Better read something about
this topic.

No clue. Just when I think I have it figured out, I find that something else
makes it error out again. The code I posted, either the above, or what I just
posted a few minutes ago in reply to Garry ("GS"), will work without error.