Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trap not working
I suspect that you might be changing worksheets in the processing and not
changing back to the original sheet but that is only a guess. However, you would be better to use code like the following to identify the Found/Not found. The space and underscore at the end of lines is simply the syntax used to insert a break in what is otherwise a full line of code. Feel free to get back to me if you are still having problems. Sub FindnProcess() Dim rngA As Range 'Range of cells in column A Dim rngB As Range 'Range of cells in column B Dim c As Range 'Each cell in column A Dim foundCell As Range 'Cell in col B where found With Sheets("Sheet1") Set rngA = .Range(.Cells(2, "A"), _ .Cells(.Rows.Count, "A").End(xlUp)) Set rngB = .Range(.Cells(2, "B"), _ .Cells(.Rows.Count, "B").End(xlUp)) End With For Each c In rngA Set foundCell = rngB.Find(What:=c.Value, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not foundCell Is Nothing Then 'Value has been found. 'Insert your found code here 'In lieu of msgbox MsgBox "Found " & c.Value Else 'Value not found. 'Insert your found code here 'In lieu of msgbox MsgBox "Not found " & c.Value End If Next c End Sub -- Regards, OssieMac "Steve C" wrote: I have a spreadsheet with about 350 names in column A. These are to be compared with a much longer list of names (about 3100) in col. B. My thought process is to select the first person in Col A, then search col. B to see if there is a match. If so, run some code based on the fact that there's a match. If the name is not found in col. B, run some other code to deal with that. Then move to the next cell in col. A and perform the search again. This would continue for all the names in col. A. When a match is not found in col. B, I'm using an error trap to deal with that situation. The problem is that I'm getting a run-time error 91 ("Object variable or With block variable not set") instead of the error-handler code being executed. I do not have any With blocks in my code. My approach in my code is based on the Edit Find command, where Excel searches only a selected range of cells for a match. Here is my code so far: Sub CompareColumns() Dim ColACount as Integer Dim ColAAddress As String, ColAName As String, ColBRange as String 'Code here that determines number of names in Col. A (ColACount) 'Code here that names the range of cells in Col. B as ColBRange Range("A1").Select For x = 1 To ColACount ColAAddress = ActiveCell.Address ColAName = ActiveCell.Value Range("ColBRange").Select 'selects all entries in col. B so that only that range is searched On Error GoTo DoThisInstead 'if name not found, run this code instead Selection.Find(What:=ColAName, After:= _ ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate 'Run code here where match is found, then Range(ColAAddress).Offset(1, 0).Select 'the next cell in col. A is selected GoTo Keepgoing DoThisInstead: 'Run code here where match is not found, then Range(ColAAddress).Offset(1, 0).Select 'the next cell in col. A is selected Keepgoing: Next x End Sub The code runs smoothly for matches that are found. In addition, the error handling code fires properly for the first entry in col. A, which has no col. B match. But for subsequent non-matches, instead of the error handler code running, I get the "Object variable or With block variable not set" error. Again, I do not have any With blocks in my code. Very strange that it works for the first non-match, but not for ones after that! I'm sure there's a better way for me to compare the columns. Thanks for any assistance you can give me. -- Steve C |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need error trap | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error Trap | Excel Programming | |||
Error Trap Not Working | Excel Programming | |||
error trap | Excel Programming |