problem with With, End with...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I've got a problem with some code of mine (using Excel2003), here's a
simplified version of it that causes the error:

Sub Test()
With Worksheets("Test1").Range("A:A")
Set C = .Find(1, LookAt:=xlWhole)
If Not C Is Nothing Then
CRow = C.Row
Do
With Worksheets("Test2").Range("A:A")
Set V = .Find(Worksheets("Test1").Range("B" &
C.Row).Value, LookAt:=xlWhole)
MsgBox "V = " & Worksheets("Test2").Range("B" &
V.Row).Value
End With

Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Row <> CRow
End If
End With
End Sub


It goes thru it once, then returns an error.

Basically, the Test1 sheet is a 'codified' list of transactions, the Test2
sheet is kind of a chart that makes Test1 sheet's values more comprehensible.
The code shown above would be to generate a report.
 
Not tested, but this may work better

Set C = Worksheets("Test1").Range("A:A").Find(1, LookAt:=xlWhole)
If Not C Is Nothing Then
CRow = C.Row
Do
With Worksheets("Test2").Range("A:A")
Set V = .Find(Worksheets("Test1").Range("B" & _
C.Row).Value, LookAt:=xlWhole)
MsgBox "V = " & Worksheets("Test2").Range("B" & _
V.Row).Value
End With

Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Row <> CRow
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Still the does the same, goes thru once and then error: run-time error 91,
object variable or with block variable not set.
 
Set C = Worksheets("Test1").Range("A:A").Find(1, LookAt:=xlWhole)
If Not C Is Nothing Then
CRow = C.Row
Do
With Worksheets("Test2").Range("A:A")
Set V = .Find(Worksheets("Test1").Range("B" & _
C.Row).Value, LookAt:=xlWhole)
MsgBox "V = " & Worksheets("Test2").Range("B" & _
V.Row).Value
End With

Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Row <> CRow
Click to expand...

I am not sure it this is at the root of the problem or not; but, since Not
has a higher precedence than And, I would guess the above statement should
be...

Loop While (Not C Is Nothing) And (C.Row <> Crow)

I know the last set of parentheses are not necessary, but I like grouping my
logical expressions in them so I can see what is going on better.

Rick
 
You're doing a second find that's screwing up the .findnext().

Should it be using the "set c = .find(...)" line
or should it be using the "Set V = .find(...)" line?

And if C isn't found then c.row will cause an error.

And I'd recommend that you provide all the parms to those .find statements.
Excel/VBA remember the last settings that were done--either manually or via
code.

Anyway, I'd do something like:

Option Explicit
Sub Test()
Dim FoundCell1 As Range
Dim FoundCell2 As Range
Dim RngToSearch1 As Range
Dim RngToSearch2 As Range
Dim FirstRow As Long
Dim WhatToFind As Long

WhatToFind = 1

Set RngToSearch1 = Worksheets("Test1").Range("A:A")
Set RngToSearch2 = Worksheets("Test2").Range("A:A")

'look for the first one
With RngToSearch1
Set FoundCell1 = .Cells.Find(what:=WhatToFind, _
LookAt:=xlWhole, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell1 Is Nothing Then
'do nothing
Else
FirstRow = FoundCell1.Row
Do
With RngToSearch2
Set FoundCell2 = .Find(what:=FoundCell1.Offset(0, 1).Value, _
LookAt:=xlWhole, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell2 Is Nothing Then
'do nothing???
Else
MsgBox FoundCell2.Offset(0, 1).Value
End If

'look for next one
Set FoundCell1 = RngToSearch1.Find(what:=WhatToFind, _
LookAt:=xlWhole, _
after:=FoundCell1, _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

'since we're in this loop, foundcell1 should never be nothing
'(we found it once to get here).
'So you don't need to check to see if foundcell1 is nothing,
'but it doesn't hurt.
'But if you're changing that foundcell1, you may want to keep
'checking.

If FoundCell1 Is Nothing Then
Exit Do
ElseIf FoundCell1.Row = FirstRow Then
Exit Do
End If
Loop
End If
End Sub
 
Set C = Worksheets("Test1").Range("A:A").Find(1, LookAt:=xlWhole)
I am not sure it this is at the root of the problem or not; but, since Not
has a higher precedence than And, I would guess the above statement should
be...

Loop While (Not C Is Nothing) And (C.Row <> Crow)

I know the last set of parentheses are not necessary, but I like grouping
my logical expressions in them so I can see what is going on better.
Click to expand...

In thinking about it again... of course, the original statement is
equivalent to what I posted; so, it can't be at the root of the problem
(unless the Not was supposed to be applied to the And'ed statements after
they were And'ed). However, I still think it is a good idea to use the
parentheses.

Rick
 
Back
Top