Loop problem

Here is my code (it is the beginning of something that will be longer):

Sub ByQuestion()

ByQuestionReport.InitializeDeclerations

Dim BQStart As Range
Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange

If WSCri.Range("C28").Value = "Provider" Then

BQStart.Select

Do Until BQStart.Value = ""

If ActiveCell = 1 Then

' ActiveCell.Offset(-1, 0).Copy
' ActiveCell.PasteSpecial (xlPasteValues)

End If

BQStart.Offset(0, 1).Select
Set BQStart = ActiveCell

Loop

End If

End Sub

The problem is that the code loops fine until it gets to a cell with a value
in it of #DIV/0! When it hits such a cell I get a type mismatch error???


--
Thanks
Shawn

Since you have a range object I would avoid tha active cell thing. As fo rthe
error you have a couple of choices. On Error Resume Next / On Error Goto 0 or
(in my opinion a better way to go change the formula to avoid creating the
error.

If(Denominator cell = 0, 0, Perform division)

Option 1
Sub ByQuestion()

ByQuestionReport.InitializeDeclerations

Dim BQStart As Range
Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange

If WSCri.Range("C28").Value = "Provider" Then
On Error Resume Next
Do Until BQStart.Value = ""

If BQStart.value = 1 Then

BQStart.Offset(-1, 0).Copy
BQStart.PasteSpecial (xlPasteValues)

End If

Set BQStart = BQStart.offest(0,1)

Loop
On Error Goto Zero
End If

End Sub

--
HTH...

Jim Thomlinson


"Shawn" wrote:

> Here is my code (it is the beginning of something that will be longer):
>
> Sub ByQuestion()
>
> ByQuestionReport.InitializeDeclerations
>
> Dim BQStart As Range
> Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange
>
> If WSCri.Range("C28").Value = "Provider" Then
>
> BQStart.Select
>
> Do Until BQStart.Value = ""
>
> If ActiveCell = 1 Then
>
> ' ActiveCell.Offset(-1, 0).Copy
> ' ActiveCell.PasteSpecial (xlPasteValues)
>
> End If
>
> BQStart.Offset(0, 1).Select
> Set BQStart = ActiveCell
>
> Loop
>
> End If
>
> End Sub
>
> The problem is that the code loops fine until it gets to a cell with a value
> in it of #DIV/0! When it hits such a cell I get a type mismatch error???
>
>
> --
> Thanks
> Shawn

I copied and pasted your code over mine. When the code gets to the On Error
Goto 0 it says there is no label or something?
--
Thanks
Shawn


"Jim Thomlinson" wrote:

> Since you have a range object I would avoid tha active cell thing. As fo rthe
> error you have a couple of choices. On Error Resume Next / On Error Goto 0 or
> (in my opinion a better way to go change the formula to avoid creating the
> error.
>
> If(Denominator cell = 0, 0, Perform division)
>
> Option 1
> Sub ByQuestion()
>
> ByQuestionReport.InitializeDeclerations
>
> Dim BQStart As Range
> Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange
>
> If WSCri.Range("C28").Value = "Provider" Then
> On Error Resume Next
> Do Until BQStart.Value = ""
>
> If BQStart.value = 1 Then
>
> BQStart.Offset(-1, 0).Copy
> BQStart.PasteSpecial (xlPasteValues)
>
> End If
>
> Set BQStart = BQStart.offest(0,1)
>
> Loop
> On Error Goto Zero
> End If
>
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Shawn" wrote:
>
> > Here is my code (it is the beginning of something that will be longer):
> >
> > Sub ByQuestion()
> >
> > ByQuestionReport.InitializeDeclerations
> >
> > Dim BQStart As Range
> > Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange
> >
> > If WSCri.Range("C28").Value = "Provider" Then
> >
> > BQStart.Select
> >
> > Do Until BQStart.Value = ""
> >
> > If ActiveCell = 1 Then
> >
> > ' ActiveCell.Offset(-1, 0).Copy
> > ' ActiveCell.PasteSpecial (xlPasteValues)
> >
> > End If
> >
> > BQStart.Offset(0, 1).Select
> > Set BQStart = ActiveCell
> >
> > Loop
> >
> > End If
> >
> > End Sub
> >
> > The problem is that the code loops fine until it gets to a cell with a value
> > in it of #DIV/0! When it hits such a cell I get a type mismatch error???
> >
> >
> > --
> > Thanks
> > Shawn

I typed in the word zero by accident. I have been doing documentation all day
and actual numbers are just not working out for me.. it shoud be

Sub Your Sub()
On Error Resume Next
'your loop
On Error GoTo 0
End Sub

My appologies...
--
HTH...

Jim Thomlinson


"Shawn" wrote:

> I copied and pasted your code over mine. When the code gets to the On Error
> Goto 0 it says there is no label or something?
> --
> Thanks
> Shawn
>
>
> "Jim Thomlinson" wrote:
>
> > Since you have a range object I would avoid tha active cell thing. As fo rthe
> > error you have a couple of choices. On Error Resume Next / On Error Goto 0 or
> > (in my opinion a better way to go change the formula to avoid creating the
> > error.
> >
> > If(Denominator cell = 0, 0, Perform division)
> >
> > Option 1
> > Sub ByQuestion()
> >
> > ByQuestionReport.InitializeDeclerations
> >
> > Dim BQStart As Range
> > Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange
> >
> > If WSCri.Range("C28").Value = "Provider" Then
> > On Error Resume Next
> > Do Until BQStart.Value = ""
> >
> > If BQStart.value = 1 Then
> >
> > BQStart.Offset(-1, 0).Copy
> > BQStart.PasteSpecial (xlPasteValues)
> >
> > End If
> >
> > Set BQStart = BQStart.offest(0,1)
> >
> > Loop
> > On Error Goto Zero
> > End If
> >
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Shawn" wrote:
> >
> > > Here is my code (it is the beginning of something that will be longer):
> > >
> > > Sub ByQuestion()
> > >
> > > ByQuestionReport.InitializeDeclerations
> > >
> > > Dim BQStart As Range
> > > Set BQStart = ThisWorkbook.Names("BQStart").RefersToRange
> > >
> > > If WSCri.Range("C28").Value = "Provider" Then
> > >
> > > BQStart.Select
> > >
> > > Do Until BQStart.Value = ""
> > >
> > > If ActiveCell = 1 Then
> > >
> > > ' ActiveCell.Offset(-1, 0).Copy
> > > ' ActiveCell.PasteSpecial (xlPasteValues)
> > >
> > > End If
> > >
> > > BQStart.Offset(0, 1).Select
> > > Set BQStart = ActiveCell
> > >
> > > Loop
> > >
> > > End If
> > >
> > > End Sub
> > >
> > > The problem is that the code loops fine until it gets to a cell with a value
> > > in it of #DIV/0! When it hits such a cell I get a type mismatch error???
> > >
> > >
> > > --
> > > Thanks
> > > Shawn