I am getting an error with this code, which was pieced together from various
older discussions from right here...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim custLookup As Range, lRow As Long
Dim i As Range
If Target.Cells.Count > 1 Or _
Target.Value = "" Then Exit Sub
With Sheets(2)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Select Case Target.Column
Case Is = 1
Set custLookup = Sheets(2).Columns("A").Find( _
What:=Target.Value, _
LookIn:=xlValues, _
MatchCase:=False)
For Each i In rng '< -- error occurs here
If i.custLookup Is Nothing Then '< -- error occurs here too
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
Else
If MsgBox("This customer already exists. " & _
"Would you like to add them again?", _
vbYesNo) = vbYes Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
End If
End If
Next i
Case Is = 2
' do stuff
End Select
End Sub

The code errors on this line:
For Each i In rng

Excel says i = nothing.
I don't understand why. Please help.

Thanks,
Ryan--


--
RyGuy

I don't see where you have defined what the variable rng represents.
There should be a Set rng = 'Some range statement and I don't see it.

"ryguy7272" wrote:

> I am getting an error with this code, which was pieced together from various
> older discussions from right here...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim custLookup As Range, lRow As Long
> Dim i As Range
> If Target.Cells.Count > 1 Or _
> Target.Value = "" Then Exit Sub
> With Sheets(2)
> lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> End With
> Select Case Target.Column
> Case Is = 1
> Set custLookup = Sheets(2).Columns("A").Find( _
> What:=Target.Value, _
> LookIn:=xlValues, _
> MatchCase:=False)
> For Each i In rng '< -- error occurs here
> If i.custLookup Is Nothing Then '< -- error occurs here too
> i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> Else
> If MsgBox("This customer already exists. " & _
> "Would you like to add them again?", _
> vbYesNo) = vbYes Then
> i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> End If
> End If
> Next i
> Case Is = 2
> ' do stuff
> End Select
> End Sub
>
> The code errors on this line:
> For Each i In rng
>
> Excel says i = nothing.
> I don't understand why. Please help.
>
> Thanks,
> Ryan--
>
>
> --
> RyGuy

rng is not declared... Dim rng as Range
rng is not pointing to anything... Set rng = Range("A1", Cells(lRow,1))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"ryguy7272"
wrote in message
I am getting an error with this code, which was pieced together from various
older discussions from right here...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim custLookup As Range, lRow As Long
Dim i As Range
If Target.Cells.Count > 1 Or _
Target.Value = "" Then Exit Sub
With Sheets(2)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Select Case Target.Column
Case Is = 1
Set custLookup = Sheets(2).Columns("A").Find( _
What:=Target.Value, _
LookIn:=xlValues, _
MatchCase:=False)
For Each i In rng '< -- error occurs here
If i.custLookup Is Nothing Then '< -- error occurs here too
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
Else
If MsgBox("This customer already exists. " & _
"Would you like to add them again?", _
vbYesNo) = vbYes Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
End If
End If
Next i
Case Is = 2
' do stuff
End Select
End Sub

The code errors on this line:
For Each i In rng

Excel says i = nothing.
I don't understand why. Please help.

Thanks,
Ryan--
--
RyGuy

Thanks JLGWhiz!

The error is gone, but Excel doesn't copy/paste anything when it runs. This
is what I have now:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim custLookup As Range, lRow As Long
Dim i As Range
If Target.Cells.Count > 1 Or _
Target.Value = "" Then Exit Sub
With Sheets(2)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Select Case Target.Column
Case Is = 1
Set custLookup = Sheets(2).Columns("A").Find( _
What:=Target.Value, _
LookIn:=xlValues, _
MatchCase:=False)
Set rng = Range("A1:C10")
For Each i In rng
If custLookup Is Nothing Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
Else
If MsgBox("This customer already exists. " & _
"Would you like to add them again?", _
vbYesNo) = vbYes Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
End If
End If
Next i
Case Is = 2
' do stuff
End Select
End Sub



--
RyGuy


"JLGWhiz" wrote:

> I don't see where you have defined what the variable rng represents.
> There should be a Set rng = 'Some range statement and I don't see it.
>
> "ryguy7272" wrote:
>
> > I am getting an error with this code, which was pieced together from various
> > older discussions from right here...
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim custLookup As Range, lRow As Long
> > Dim i As Range
> > If Target.Cells.Count > 1 Or _
> > Target.Value = "" Then Exit Sub
> > With Sheets(2)
> > lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> > End With
> > Select Case Target.Column
> > Case Is = 1
> > Set custLookup = Sheets(2).Columns("A").Find( _
> > What:=Target.Value, _
> > LookIn:=xlValues, _
> > MatchCase:=False)
> > For Each i In rng '< -- error occurs here
> > If i.custLookup Is Nothing Then '< -- error occurs here too
> > i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> > Else
> > If MsgBox("This customer already exists. " & _
> > "Would you like to add them again?", _
> > vbYesNo) = vbYes Then
> > i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> > End If
> > End If
> > Next i
> > Case Is = 2
> > ' do stuff
> > End Select
> > End Sub
> >
> > The code errors on this line:
> > For Each i In rng
> >
> > Excel says i = nothing.
> > I don't understand why. Please help.
> >
> > Thanks,
> > Ryan--
> >
> >
> > --
> > RyGuy

Hi RyGuy

Your variable "rng" is not declared in your code i.e. Dim rng as
range, also you are trying to loop through "rng" which is not set to
anything i.e. set rng=range("A1:A100")

You then go on to "if i.custlookup" this wont work as you are
effectively saying range.range???

if you are looking for a cell ("i") in the range ("rng") that contains
the same value as the range ("CustLookUp") you could write it as

For Each i In rng
If i.value=custlookup.value then

or if you want the cells that do not hold the same value as custlookup
you could write it as

For Each i In rng '< -- error occurs here
If i.value <> custlookup.value then


I hope this helps you out

Steve

WOW!!! That did it Jim! Thanks all! Final version below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim custLookup As Range, lRow As Long
Dim i As Range
If Target.Cells.Count > 1 Or _
Target.Value = "" Then Exit Sub
With Sheets(2)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Select Case Target.Column
Case Is = 1
Set custLookup = Sheets(2).Columns("A").Find( _
What:=Target.Value, _
LookIn:=xlValues, _
MatchCase:=False)
Set rng = Range("A1", Cells(lRow, 1))
For Each i In rng
If custLookup Is Nothing Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
Else
If MsgBox("This customer already exists. " & _
"Would you like to add them again?", _
vbYesNo) = vbYes Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
End If
End If
Next i
Case Is = 2
' do stuff
End Select
End Sub



Regards,
Ryan--


--
RyGuy


"Incidental" wrote:

> Hi RyGuy
>
> Your variable "rng" is not declared in your code i.e. Dim rng as
> range, also you are trying to loop through "rng" which is not set to
> anything i.e. set rng=range("A1:A100")
>
> You then go on to "if i.custlookup" this wont work as you are
> effectively saying range.range???
>
> if you are looking for a cell ("i") in the range ("rng") that contains
> the same value as the range ("CustLookUp") you could write it as
>
> For Each i In rng
> If i.value=custlookup.value then
>
> or if you want the cells that do not hold the same value as custlookup
> you could write it as
>
> For Each i In rng '< -- error occurs here
> If i.value <> custlookup.value then
>
>
> I hope this helps you out
>
> Steve
>
>
>

I am not really sure what you are trying to do, but I think you want to
change this line from:

If custLookup Is Nothing Then

To:

If i <> custLookup Then

When I do complex find and compare procedures, I find it useful to take a
pencil and paper and draw out my logic sequence of events to make sure I am
not looking in air holes.

"ryguy7272" wrote:

> Thanks JLGWhiz!
>
> The error is gone, but Excel doesn't copy/paste anything when it runs. This
> is what I have now:
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim custLookup As Range, lRow As Long
> Dim i As Range
> If Target.Cells.Count > 1 Or _
> Target.Value = "" Then Exit Sub
> With Sheets(2)
> lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> End With
> Select Case Target.Column
> Case Is = 1
> Set custLookup = Sheets(2).Columns("A").Find( _
> What:=Target.Value, _
> LookIn:=xlValues, _
> MatchCase:=False)
> Set rng = Range("A1:C10")
> For Each i In rng
> If custLookup Is Nothing Then
> i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> Else
> If MsgBox("This customer already exists. " & _
> "Would you like to add them again?", _
> vbYesNo) = vbYes Then
> i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> End If
> End If
> Next i
> Case Is = 2
> ' do stuff
> End Select
> End Sub
>
>
>
> --
> RyGuy
>
>
> "JLGWhiz" wrote:
>
> > I don't see where you have defined what the variable rng represents.
> > There should be a Set rng = 'Some range statement and I don't see it.
> >
> > "ryguy7272" wrote:
> >
> > > I am getting an error with this code, which was pieced together from various
> > > older discussions from right here...
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim custLookup As Range, lRow As Long
> > > Dim i As Range
> > > If Target.Cells.Count > 1 Or _
> > > Target.Value = "" Then Exit Sub
> > > With Sheets(2)
> > > lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> > > End With
> > > Select Case Target.Column
> > > Case Is = 1
> > > Set custLookup = Sheets(2).Columns("A").Find( _
> > > What:=Target.Value, _
> > > LookIn:=xlValues, _
> > > MatchCase:=False)
> > > For Each i In rng '< -- error occurs here
> > > If i.custLookup Is Nothing Then '< -- error occurs here too
> > > i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> > > Else
> > > If MsgBox("This customer already exists. " & _
> > > "Would you like to add them again?", _
> > > vbYesNo) = vbYes Then
> > > i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
> > > End If
> > > End If
> > > Next i
> > > Case Is = 2
> > > ' do stuff
> > > End Select
> > > End Sub
> > >
> > > The code errors on this line:
> > > For Each i In rng
> > >
> > > Excel says i = nothing.
> > > I don't understand why. Please help.
> > >
> > > Thanks,
> > > Ryan--
> > >
> > >
> > > --
> > > RyGuy