software

Howdy,
So far I have code to match numbers from my quot;rowsquot; sheet to my quot;auditquot;
sheet. What I need to do is if a value is not found, take that value
and paste it onto my quot;missingquot; 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(quot;This may take a few minutes...quot; _
amp; quot;are you sure you want to populate the audit?quot;, vbYesNo)
If pop = vbYes Then
Application.ScreenUpdating = False
myCols = Array(quot;Aquot;, quot;Dquot;, quot;Gquot;, quot;Jquot;)
'use the same name for consistency
Set myRng = Worksheets(quot;rowsquot;).Range(quot;myrngquot;)
For cCtr = LBound(myCols) To UBound(myCols)
With Worksheets(quot;auditquot;)
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 = quot;Processing: quot; amp; 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
quot;PASTE TO quot;MISSINGquot; SHEET ON NEXT EMPTY CELL IN COLUMN A quot;
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
End If
Next myCell
Next cCtr
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox quot;Done!quot;
Else
'do nothing
End If
End Sub
Appreciate the help.
Mjack--
mjack003
------------------------------------------------------------------------
mjack003's Profile: www.excelforum.com/member.php...foamp;userid=5141
View this thread: www.excelforum.com/showthread...hreadid=493695First, 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(quot;This may take a few minutes...quot; _
amp; quot;are you sure you want to populate the audit?quot;, vbYesNo)

If pop = vbYes Then
Application.ScreenUpdating = False
myCols = Array(quot;Aquot;, quot;Dquot;, quot;Gquot;, quot;Jquot;)
'use the same name for consistency
Set myRng = Worksheets(quot;rowsquot;).Range(quot;myrngquot;)
For cCtr = LBound(myCols) To UBound(myCols)
With Worksheets(quot;auditquot;)
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 = quot;Processing: quot; amp; 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(quot;Missingquot;)
Set DestCellMissing _
= .Cells(.Rows.Count, quot;Aquot;).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 quot;Done!quot;
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.

mjack003 wrote:
gt;
gt; Howdy,
gt; So far I have code to match numbers from my quot;rowsquot; sheet to my quot;auditquot;
gt; sheet. What I need to do is if a value is not found, take that value
gt; and paste it onto my quot;missingquot; sheet in the next empty cell in column
gt; A. I've tried various ways but can not seem to figure this one out.
gt; Here is what I have so far.
gt; Private Sub CommandButton3_Click()
gt; Dim myRng As Range
gt; Dim myCell As Range
gt; Dim myInputRng As Range
gt; Dim FoundCell As Range
gt; Dim pop As Long
gt; Dim myCols As Variant
gt; Dim cCtr As Long
gt; pop = MsgBox(quot;This may take a few minutes...quot; _
gt; amp; quot;are you sure you want to populate the audit?quot;, vbYesNo)
gt; If pop = vbYes Then
gt; Application.ScreenUpdating = False
gt; myCols = Array(quot;Aquot;, quot;Dquot;, quot;Gquot;, quot;Jquot;)
gt; 'use the same name for consistency
gt; Set myRng = Worksheets(quot;rowsquot;).Range(quot;myrngquot;)
gt; For cCtr = LBound(myCols) To UBound(myCols)
gt; With Worksheets(quot;auditquot;)
gt; Set myInputRng = .Range(.Cells(2, myCols(cCtr)), _
gt; Cells(.Rows.Count, myCols(cCtr)).End(xlUp))
gt; End With
gt; myInputRng.Offset(0, 1).ClearContents
gt; For Each myCell In myInputRng.Cells
gt; Application.StatusBar = quot;Processing: quot; amp; myCell.Address(0, 0)
gt; If myCell.Value = 0 Then
gt; Else
gt; Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
gt; lookat:=xlWhole, LookIn:=xlValues, _
gt; MatchCase:=False, searchorder:=xlByRows)
gt; If FoundCell Is Nothing Then
gt; quot;PASTE TO quot;MISSINGquot; SHEET ON NEXT EMPTY CELL IN COLUMN A quot;
gt; Else
gt; myCell.Offset(0, 1).Value = FoundCell.column - 1
gt; End If
gt; End If
gt; Next myCell
gt; Next cCtr
gt; Application.ScreenUpdating = True
gt; Application.StatusBar = False
gt; MsgBox quot;Done!quot;
gt; Else
gt; 'do nothing
gt; End If
gt; End Sub
gt; Appreciate the help.
gt; Mjack
gt;
gt; --
gt; mjack003
gt; ------------------------------------------------------------------------
gt; mjack003's Profile: www.excelforum.com/member.php...foamp;userid=5141
gt; View this thread: www.excelforum.com/showthread...hreadid=493695

--

Dave Peterson