Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1287

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

Show all headers | View raw

Auric__ submitted this idea :
> GS wrote:
>
>> It happens that Auric__ formulated : [snip]
>>> 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>

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Run-time error '1004': Application-defined or object-defined error slimeph@gmail.com - 2012-07-09 11:27 -0700
  Re: Run-time error '1004': Application-defined or object-defined error GS <gs@somewhere.net> - 2012-07-09 18:13 -0400
    Re: Run-time error '1004': Application-defined or object-defined error slimeph@gmail.com - 2012-07-10 05:36 -0700
      Re: Run-time error '1004': Application-defined or object-defined error GS <gs@somewhere.net> - 2012-07-10 11:42 -0400
        Re: Run-time error '1004': Application-defined or object-defined error "Auric__" <not.my.real@email.address> - 2012-07-10 17:03 +0000
          Re: Run-time error '1004': Application-defined or object-defined error GS <gs@somewhere.net> - 2012-07-10 13:27 -0400
            Re: Run-time error '1004': Application-defined or object-defined error "Auric__" <not.my.real@email.address> - 2012-07-10 18:26 +0000
              Re: Run-time error '1004': Application-defined or object-defined error GS <gs@somewhere.net> - 2012-07-10 16:28 -0400
                Re: Run-time error '1004': Application-defined or object-defined error "Auric__" <not.my.real@email.address> - 2012-07-10 23:10 +0000
                Re: Run-time error '1004': Application-defined or object-defined error GS <gs@somewhere.net> - 2012-07-10 21:21 -0400
              Re: Run-time error '1004': Application-defined or object-defined error slimeph@gmail.com - 2012-07-10 15:26 -0700
                Re: Run-time error '1004': Application-defined or object-defined error "Auric__" <not.my.real@email.address> - 2012-07-10 23:24 +0000

csiph-web