Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem generating list
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem generating list
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. mjack003 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List box setup, placement, and functionality | New Users to Excel | |||
Filtered list & maximum no columns problem | Excel Discussion (Misc queries) | |||
Ooh .. Linking a list to a list to an output cell | Excel Discussion (Misc queries) | |||
Update master list with other lists | Excel Worksheet Functions | |||
Dependent List Query | Excel Worksheet Functions |