problem with With, End with...
 

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.

problem with With, End with...
 

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)

"William" wrote in message
...
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.

problem with With, End with...
 

Still the does the same, goes thru once and then error: run-time error 91,
object variable or with block variable not set.


"Bob Phillips" wrote:

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)

"William" wrote in message
...
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.

problem with With, End with...
 

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

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

problem with With, End with...
 

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




William wrote:

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.

--

Dave Peterson

problem with With, End with...
 

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

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.

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