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 value
and paste it onto my "missing" sheet in the next empty cell in column
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.
Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=493695