Issue with Cells.Find statement within With
 

Hi,

The following code is working, however, it takes very long for it to be
executed (I've let it run for 2 minutes -- it hasn't completed in that amount
of time). When I stop the query by hitting the ESC key, the error below
comes up. I looked at the explanation in the Help menu for the error, but
did not understand why this is coming up. Any help is appreciated (FYI -
this code is a workaround to the issue I posted in my "New issue with "With"
statement" on 6/8/07).

The code below with "<<" at the beginning of the line is the code that is
highlighted as an error.

Error Message:
Run-time error '91': Object variable or With block variable not set

Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim y 'variable to determine how many rows down to copy from "Member ID
Report Master" sheet
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim merchantname
Dim salescom

With Sheets("Unique Member IDs")
If Len(.Range("A3")) < 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) < 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then

With Sheets("Member ID Report Master")
<< memberid = .Cells.Find(What:=F).Offset(0, -3).Value
merchantid = .Cells.Find(What:=F).Offset(0, -2).Value
merchantname = .Cells.Find(What:=F).Offset(0, -1).Value
salescom = .Cells.Find(What:=F).Offset(0, 6).Value
End With

With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = merchantname
.Range("D1").Offset(x, 1).Value = salescom
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"
x = x + 1
End With

Else
End If

Next

End Sub



--
Robert

Issue with Cells.Find statement within With
 

If one of those .find's fail, then you'll get this kind of error.

I'd use something like:

Dim FoundCell as Range
''''''

With Sheets("Member ID Report Master")
set foundcell = .cells.find(what:=f)
if foundcell is nothing then
'it wasn't found, skip it???
else
memberid = foundcell.Offset(0, -3).Value
merchantid = foundcell.Offset(0, -2).Value
merchantname = foundcell.Offset(0, -1).Value
salescom = foundcell.Offset(0, 6).Value
end if
End With

I'd also provide all the parms to the .find statement and I'd check to see what
column Foundcell was before I tried to use .offset() against it.



robs3131 wrote:

Hi,

The following code is working, however, it takes very long for it to be
executed (I've let it run for 2 minutes -- it hasn't completed in that amount
of time). When I stop the query by hitting the ESC key, the error below
comes up. I looked at the explanation in the Help menu for the error, but
did not understand why this is coming up. Any help is appreciated (FYI -
this code is a workaround to the issue I posted in my "New issue with "With"
statement" on 6/8/07).

The code below with "<<" at the beginning of the line is the code that is
highlighted as an error.

Error Message:
Run-time error '91': Object variable or With block variable not set

Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim y 'variable to determine how many rows down to copy from "Member ID
Report Master" sheet
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim merchantname
Dim salescom

With Sheets("Unique Member IDs")
If Len(.Range("A3")) < 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) < 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then

With Sheets("Member ID Report Master")
<< memberid = .Cells.Find(What:=F).Offset(0, -3).Value
merchantid = .Cells.Find(What:=F).Offset(0, -2).Value
merchantname = .Cells.Find(What:=F).Offset(0, -1).Value
salescom = .Cells.Find(What:=F).Offset(0, 6).Value
End With

With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = merchantname
.Range("D1").Offset(x, 1).Value = salescom
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"
x = x + 1
End With

Else
End If

Next

End Sub

--
Robert

--

Dave Peterson

Issue with Cells.Find statement within With
 

Thanks Dave! That's a good point about ensuring that the found cell is in
the right column -- it is possible that the value I'm looking for is in more
than 1 column.
--
Robert


"Dave Peterson" wrote:

If one of those .find's fail, then you'll get this kind of error.

I'd use something like:

Dim FoundCell as Range
''''''

With Sheets("Member ID Report Master")
set foundcell = .cells.find(what:=f)
if foundcell is nothing then
'it wasn't found, skip it???
else
memberid = foundcell.Offset(0, -3).Value
merchantid = foundcell.Offset(0, -2).Value
merchantname = foundcell.Offset(0, -1).Value
salescom = foundcell.Offset(0, 6).Value
end if
End With

I'd also provide all the parms to the .find statement and I'd check to see what
column Foundcell was before I tried to use .offset() against it.



robs3131 wrote:

Hi,

The following code is working, however, it takes very long for it to be
executed (I've let it run for 2 minutes -- it hasn't completed in that amount
of time). When I stop the query by hitting the ESC key, the error below
comes up. I looked at the explanation in the Help menu for the error, but
did not understand why this is coming up. Any help is appreciated (FYI -
this code is a workaround to the issue I posted in my "New issue with "With"
statement" on 6/8/07).

The code below with "<<" at the beginning of the line is the code that is
highlighted as an error.

Error Message:
Run-time error '91': Object variable or With block variable not set

Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim y 'variable to determine how many rows down to copy from "Member ID
Report Master" sheet
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim merchantname
Dim salescom

With Sheets("Unique Member IDs")
If Len(.Range("A3")) < 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) < 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then

With Sheets("Member ID Report Master")
<< memberid = .Cells.Find(What:=F).Offset(0, -3).Value
merchantid = .Cells.Find(What:=F).Offset(0, -2).Value
merchantname = .Cells.Find(What:=F).Offset(0, -1).Value
salescom = .Cells.Find(What:=F).Offset(0, 6).Value
End With

With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = merchantname
.Range("D1").Offset(x, 1).Value = salescom
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"
x = x + 1
End With

Else
End If

Next

End Sub

--
Robert

--

Dave Peterson

Issue with Cells.Find statement within With
 

If you know what column should be searched:

with sheets("member id report master")
with .range("a:a")
set foundcell = .cells.find(.....




robs3131 wrote:

Thanks Dave! That's a good point about ensuring that the found cell is in
the right column -- it is possible that the value I'm looking for is in more
than 1 column.
--
Robert

"Dave Peterson" wrote:

If one of those .find's fail, then you'll get this kind of error.

I'd use something like:

Dim FoundCell as Range
''''''

With Sheets("Member ID Report Master")
set foundcell = .cells.find(what:=f)
if foundcell is nothing then
'it wasn't found, skip it???
else
memberid = foundcell.Offset(0, -3).Value
merchantid = foundcell.Offset(0, -2).Value
merchantname = foundcell.Offset(0, -1).Value
salescom = foundcell.Offset(0, 6).Value
end if
End With

I'd also provide all the parms to the .find statement and I'd check to see what
column Foundcell was before I tried to use .offset() against it.



robs3131 wrote:

Hi,

The following code is working, however, it takes very long for it to be
executed (I've let it run for 2 minutes -- it hasn't completed in that amount
of time). When I stop the query by hitting the ESC key, the error below
comes up. I looked at the explanation in the Help menu for the error, but
did not understand why this is coming up. Any help is appreciated (FYI -
this code is a workaround to the issue I posted in my "New issue with "With"
statement" on 6/8/07).

The code below with "<<" at the beginning of the line is the code that is
highlighted as an error.

Error Message:
Run-time error '91': Object variable or With block variable not set

Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim y 'variable to determine how many rows down to copy from "Member ID
Report Master" sheet
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim merchantname
Dim salescom

With Sheets("Unique Member IDs")
If Len(.Range("A3")) < 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) < 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then

With Sheets("Member ID Report Master")
<< memberid = .Cells.Find(What:=F).Offset(0, -3).Value
merchantid = .Cells.Find(What:=F).Offset(0, -2).Value
merchantname = .Cells.Find(What:=F).Offset(0, -1).Value
salescom = .Cells.Find(What:=F).Offset(0, 6).Value
End With

With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = merchantname
.Range("D1").Offset(x, 1).Value = salescom
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"
x = x + 1
End With

Else
End If

Next

End Sub

--
Robert

--

Dave Peterson


--

Dave Peterson

Issue with Cells.Find statement within With
 

Thanks again! I thought of the same thing earlier today.
--
Robert


"robs3131" wrote:

Hi,

The following code is working, however, it takes very long for it to be
executed (I've let it run for 2 minutes -- it hasn't completed in that amount
of time). When I stop the query by hitting the ESC key, the error below
comes up. I looked at the explanation in the Help menu for the error, but
did not understand why this is coming up. Any help is appreciated (FYI -
this code is a workaround to the issue I posted in my "New issue with "With"
statement" on 6/8/07).

The code below with "<<" at the beginning of the line is the code that is
highlighted as an error.

Error Message:
Run-time error '91': Object variable or With block variable not set

Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim y 'variable to determine how many rows down to copy from "Member ID
Report Master" sheet
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim merchantname
Dim salescom

With Sheets("Unique Member IDs")
If Len(.Range("A3")) < 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) < 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then

With Sheets("Member ID Report Master")
<< memberid = .Cells.Find(What:=F).Offset(0, -3).Value
merchantid = .Cells.Find(What:=F).Offset(0, -2).Value
merchantname = .Cells.Find(What:=F).Offset(0, -1).Value
salescom = .Cells.Find(What:=F).Offset(0, 6).Value
End With

With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = merchantname
.Range("D1").Offset(x, 1).Value = salescom
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"
x = x + 1
End With

Else
End If

Next

End Sub



--
Robert