Auto Copy from Sheet1 to Sheet2

[ryguy7272]

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--

 

[JLGWhiz]

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.

 

[Jim Cone]

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--

 

[ryguy7272]

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

 

[Incidental]

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

 

[ryguy7272]

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--

 

[JLGWhiz]

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.