Clearing Errors
I have tried hard to make this code work but I have not been successful. Sheets("CustData").Activate Num = Sheets("CustData").Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) Sheets("CustData").Range(Cells(6, 1), Cells(Num + 10, 1)).Select On Error GoTo Line2 Selection.Find(What:=CustN, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Activate Row = ActiveCell.Row Sheets("CustData").Range(Cells(Row, 1), Cells(Row, 110)).ClearContents Sheets("CustData").Cells(Row, 112).ClearContents Line2: If Err.Number < 0 Then Err.Clear Next x In this example CustN="" for all x's. The code works fine for the first x. It jumps to Line2 and proceeds to the next x. However the "Find" line fails with a runtime error 91 when x =2, 3 or 4. What am I doing wrong? Thanks for your help. Jim Walsh |
Clearing Errors
This should help you find the problem
with Sheets("CustData") Num = .Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) end if set cell = .Range(Range("A6"), Range("A" & (Num + 10)) set c = cell.Find(What:=CustN, _ LookIn:=xlValues, _ LookAt:= xlWhole) if not c is nothing then Row = c.Row .Range(.Range("A" & Row), .Range("DF" & Row).ClearContents .Range("DH" & Row).ClearContents end if Next x end with "jswalsh33" wrote: I have tried hard to make this code work but I have not been successful. Sheets("CustData").Activate Num = Sheets("CustData").Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) Sheets("CustData").Range(Cells(6, 1), Cells(Num + 10, 1)).Select On Error GoTo Line2 Selection.Find(What:=CustN, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Activate Row = ActiveCell.Row Sheets("CustData").Range(Cells(Row, 1), Cells(Row, 110)).ClearContents Sheets("CustData").Cells(Row, 112).ClearContents Line2: If Err.Number < 0 Then Err.Clear Next x In this example CustN="" for all x's. The code works fine for the first x. It jumps to Line2 and proceeds to the next x. However the "Find" line fails with a runtime error 91 when x =2, 3 or 4. What am I doing wrong? Thanks for your help. Jim Walsh |
Clearing Errors
Joel,
Thank you very much. The code worked very well after I removed the dots in front of the Ranges. They gave me compile errors. I would appreciate it if you could tell me why the code I wrote did not work. Jim Walsh "Joel" wrote: This should help you find the problem with Sheets("CustData") Num = .Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) end if set cell = .Range(Range("A6"), Range("A" & (Num + 10)) set c = cell.Find(What:=CustN, _ LookIn:=xlValues, _ LookAt:= xlWhole) if not c is nothing then Row = c.Row .Range(.Range("A" & Row), .Range("DF" & Row).ClearContents .Range("DH" & Row).ClearContents end if Next x end with "jswalsh33" wrote: I have tried hard to make this code work but I have not been successful. Sheets("CustData").Activate Num = Sheets("CustData").Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) Sheets("CustData").Range(Cells(6, 1), Cells(Num + 10, 1)).Select On Error GoTo Line2 Selection.Find(What:=CustN, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Activate Row = ActiveCell.Row Sheets("CustData").Range(Cells(Row, 1), Cells(Row, 110)).ClearContents Sheets("CustData").Cells(Row, 112).ClearContents Line2: If Err.Number < 0 Then Err.Clear Next x In this example CustN="" for all x's. The code works fine for the first x. It jumps to Line2 and proceeds to the next x. However the "Find" line fails with a runtime error 91 when x =2, 3 or 4. What am I doing wrong? Thanks for your help. Jim Walsh |
Clearing Errors
You should of removed the periods, instead add them to this line
from set cell = .Range(Range("A6"), Range("A" & (Num + 10)) to set cell = .Range(.Range("A6"), .Range("A" & (Num + 10)) You want to make sure the correct sheet si being referenced and without the periods the wrong sheet may be selected. "jswalsh33" wrote: Joel, Thank you very much. The code worked very well after I removed the dots in front of the Ranges. They gave me compile errors. I would appreciate it if you could tell me why the code I wrote did not work. Jim Walsh "Joel" wrote: This should help you find the problem with Sheets("CustData") Num = .Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) end if set cell = .Range(Range("A6"), Range("A" & (Num + 10)) set c = cell.Find(What:=CustN, _ LookIn:=xlValues, _ LookAt:= xlWhole) if not c is nothing then Row = c.Row .Range(.Range("A" & Row), .Range("DF" & Row).ClearContents .Range("DH" & Row).ClearContents end if Next x end with "jswalsh33" wrote: I have tried hard to make this code work but I have not been successful. Sheets("CustData").Activate Num = Sheets("CustData").Range("DG5001") For x = 1 To 4 If x = 1 Then CustN = Sheets("OrderEntry").Range("D7") Else CustN = Sheets("OrderEntry").Cells((x - 2) * 14 + 23, 4) Sheets("CustData").Range(Cells(6, 1), Cells(Num + 10, 1)).Select On Error GoTo Line2 Selection.Find(What:=CustN, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Activate Row = ActiveCell.Row Sheets("CustData").Range(Cells(Row, 1), Cells(Row, 110)).ClearContents Sheets("CustData").Cells(Row, 112).ClearContents Line2: If Err.Number < 0 Then Err.Clear Next x In this example CustN="" for all x's. The code works fine for the first x. It jumps to Line2 and proceeds to the next x. However the "Find" line fails with a runtime error 91 when x =2, 3 or 4. What am I doing wrong? Thanks for your help. Jim Walsh |
All times are GMT +1. The time now is 02:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com