Problem generating list

[mjack003]

Howdy,
So far I have code to match numbers from my "rows" sheet to my "audit
sheet. What I need to do is if a value is not found, take that valu
and paste it onto my "missing" sheet in the next empty cell in colum
A. I've tried various ways but can not seem to figure this one out.
Here is what I have so far.
Private Sub CommandButton3_Click()
Dim myRng As Range
Dim myCell As Range
Dim myInputRng As Range
Dim FoundCell As Range
Dim pop As Long
Dim myCols As Variant
Dim cCtr As Long
pop = MsgBox("This may take a few minutes..." _
& "are you sure you want to populate the audit?", vbYesNo)
If pop = vbYes Then
Application.ScreenUpdating = False
myCols = Array("A", "D", "G", "J")
'use the same name for consistency
Set myRng = Worksheets("rows").Range("myrng")
For cCtr = LBound(myCols) To UBound(myCols)
With Worksheets("audit")
Set myInputRng = .Range(.Cells(2, myCols(cCtr)), _
.Cells(.Rows.Count, myCols(cCtr)).End(xlUp))
End With
myInputRng.Offset(0, 1).ClearContents
For Each myCell In myInputRng.Cells
Application.StatusBar = "Processing: " & myCell.Address(0, 0)
If myCell.Value = 0 Then
Else
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
"PASTE TO "MISSING" SHEET ON NEXT EMPTY CELL IN COLUMN A "
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
End If
Next myCell
Next cCtr
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Done!"
Else
'do nothing
End If
End Sub
Appreciate the help.
Mjac

 

[Dave Peterson]

First, since there's lots of stuff hidden in your code that depends on your
workbook (range names, where the button is), it's difficult to set up a test
workbook.

But this compiled for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim myRng As Range
Dim myCell As Range
Dim myInputRng As Range
Dim FoundCell As Range
Dim pop As Long
Dim myCols As Variant
Dim cCtr As Long
Dim DestCellMissing As Range

pop = MsgBox("This may take a few minutes..." _
& "are you sure you want to populate the audit?", vbYesNo)

If pop = vbYes Then
Application.ScreenUpdating = False
myCols = Array("A", "D", "G", "J")
'use the same name for consistency
Set myRng = Worksheets("rows").Range("myrng")
For cCtr = LBound(myCols) To UBound(myCols)
With Worksheets("audit")
Set myInputRng = .Range(.Cells(2, myCols(cCtr)), _
.Cells(.Rows.Count, myCols(cCtr)).End(xlUp))
End With
myInputRng.Offset(0, 1).ClearContents
For Each myCell In myInputRng.Cells
Application.StatusBar = "Processing: " & myCell.Address(0, 0)
If myCell.Value = 0 Then
Else
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
With Worksheets("Missing")
Set DestCellMissing _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myCell.Copy _
Destination:=DestCellMissing
End If
Else
myCell.Offset(0, 1).Value = FoundCell.Column - 1
End If
End If
Next myCell
Next cCtr
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Done!"
Else
'do nothing
End If
End Sub

This compiled for me, but I really don't know if it does what you want. You may
want to test against a copy of your workbook.