Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Clearing Errors
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 51
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 51
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Clearing cells without clearing formulas marsjune68 Excel Discussion (Misc queries) 2 April 10th 09 07:39 PM
Clearing RAM NathanG Excel Programming 2 February 16th 07 11:51 AM
Excel Throwing Circular Errors When No Errors Exist MDW Excel Worksheet Functions 1 August 10th 06 02:15 PM
Clearing #VALUE skateblade Excel Worksheet Functions 3 October 15th 05 10:34 PM
Unresolved Errors in IF Statements - Errors do not show in results Markthepain Excel Worksheet Functions 2 December 3rd 04 08:49 AM


All times are GMT +1. The time now is 12:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement