Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Loop Problem | Excel Programming | |||
Loop Problem | Excel Programming | |||
For...Each Loop Problem | Excel Programming | |||
Problem with Loop | Excel Programming |