Solved: Cancel a Loop if .Find Parameters Do Not Exist

[Rlb53]

The Code Below Performs as intended, but it locks up when parameters identified in the .find command do not exist.

How may I Exit Do if the Set c = .FindNext(c) is not found?
With Sheets("sheet2").Range("a:a")
Dim r As Range
Set r = .Find(Sheets("sheet1").ComboBox1, lookat:=xlWhole)
If r.Offset(0, 5) = "" Then
r.Offset(0, 5) = Date
r.Offset(0, 6) = Sheets("sheet1").ComboBox4
r.Offset(0, 7).FormulaR1C1 = "=sum(rc[-2]-rc[-6])"
Else

Set c = .Find(Sheets("sheet1").ComboBox1, lookat:=xlWhole)
If Not c.Offset(0, 5) Is Nothing Then
Do
Set c = .FindNext(c)
If c.Offset(0, 5) Is Nothing Then
Exit Do
End If
Loop Until c.Offset(0, 5) = ""
End If
c.Offset(0, 5) = Date
c.Offset(0, 6) = Sheets("sheet1").ComboBox4
c.Offset(0, 7).FormulaR1C1 = "=sum(rc[-2]-rc[-6])"
End If
End With

[Bob Phillips]

Test c for Nothing, not its Offset cell.

[snb]

Apparently you want to filter, so why not using a filter ?


With Sheets("sheet2").usedrange.columns(1)

.autofilter 1,.ComboBox1.value
for each cl in .specialcells(12)
if cl.Offset(0, 5) = "" Then cl.Offset(0, 5).resize(,3)=Array(Date, Sheets("sheet1").Oleobjects("ComboBox4").object.value, "=sum(rc[-2]-rc[-6])")
.autofilter
End With

[Rlb53]

Thank You,

Checking for C is Nothing (configured as shown) continues to lock up.

Private Sub CommandButton1_Click()

With Sheets("sheet2").Range("a:a")
Dim r As Range
Set r = .Find(Sheets("sheet1").ComboBox1, lookat:=xlWhole)
If r.Offset(0, 5) = "" Then
r.Offset(0, 5) = Date
r.Offset(0, 6) = Sheets("sheet1").ComboBox4
r.Offset(0, 7).FormulaR1C1 = "=sum(rc[-2]-rc[-6])"
Else
Set = .Find(Sheets("sheet1").ComboBox1, lookat:=xlWhole)
If Not c.Offset(0, 5) Is Nothing Then
Do
Set c = .FindNext(c)
If c Is Nothing Then ' HANGS UP IF c IS NOT FOUND IN RANGE "A:A"
exit do
GoTo line1
End If
If c.Offset(0, 5) Is Nothing Then
Exit Do
End If
Loop Until c.Offset(0, 5) = ""
End If
c.Offset(0, 5) = Date
c.Offset(0, 6) = Sheets("sheet1").ComboBox4
c.Offset(0, 7).FormulaR1C1 = "=sum(rc[-2]-rc[-6])"
End If
End With
Exit Sub

line1:
MsgBox "There Are No Valid Entries For This Query"



End Sub

Filtering, Yes but No. The purpose is to find the next row with Combobox1.Value - Insert Data into Offset(0,5,) (if it is null)- Exit Sub.

If there are no rows with null values in offset(0,5). GoTo Line1 should occurr. I am attaching a sample if you would review.

Thanks.

[Bob Phillips]

How can it be not found, you are selecting from lists that are based on the real data?

[Rlb53]

I've asked myself that repeatedly.

I generated a worksheet to try and figure out the issue. (uploaded)

I inserted the:
If c Is Nothing Then
Exit Do
Goto line1
End If
As you Previously noted.

I thought this would provide instruction to redirect commands should "c" be "Null" in range A:A. But... it doesn't in the current syntax.

[Bob Phillips]

What values did you select that generate the error?

[Rlb53]

If a query is made to identify .Find(Sheets("sheet1").ComboBox1, lookat:=xlWhole) and the value of .offset(0,5) does not equal " "

In this case, the condition that .FindNext(c) Is Nothing would be true.

At this point the process hangs up although I thought I placed instruction to redirect the path in this instance.

[Rlb53]

Simple enough solution.... Just could not see it (as is usually the case)

The code was in a vicious circle.

Corrected it as shown below. :

Private Sub CommandButton1_Click()
With Sheets("sheet2").Range("a:a")
Dim r As Range
Set r = .Find(Sheets("sheet1").ComboBox1, LookAt:=xlWhole)
If r.Offset(0, 5) = "" Then
r.Offset(0, 5) = Date
r.Offset(0, 6) = Sheets("sheet1").ComboBox4
r.Offset(0, 7).FormulaR1C1 = "=sum(rc[-2]-rc[-6])"
Else

Set c = .Find(Sheets("sheet1").ComboBox1, LookAt:=xlWhole)

If Not c.Offset(0, 5) Is Nothing Then
firstaddress = c.Address

Do
Set c = .FindNext(c)
Loop Until c.Offset(0, 5) = "" Or _
c.Address = firstaddress
End If

c.Offset(0, 5) = Date
c.Offset(0, 6) = Sheets("sheet1").ComboBox4
c.Offset(0, 7).FormulaR1C1 = "=sum(rc[-2]-rc[-6])"
End If
End With
End Sub

I did not have the c.address = firstaddress reference inserted.
in addition to the Loop Until firstaddress=c.address so that it would exit.

Thanks...
This one is solved...