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
|
Similar Threads
