Excel VBA find.next problem, help 1

[knifey]

Hi,
I have a piece of code that I've used many times without errors, until today. The code falls over with a error 91 on the second run of the do loop at 'Loop While Not j Is Nothing And j.Address <> firstAddress'. Also on the line before 'Set j = .FindNext(j)', j has a value of nothing. Even though there is another occurance of j to find? Can anyone spot where I've gone wrong?
Thanks for your help,
Knifey
P.S. Please copy & paste into notepad to regain tabulation.

'search field lookup file to find the legacy column title
With wb3.Worksheets(ws3.Name).Columns("C:C").Rows("1:" & endRowFieldLookup)
Set j = .Find(inputColTitle, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
'if it is found then..
If Not j Is Nothing Then
'where was it found
firstAddress = j.Address
'a continuous loop
Do
'what column?
legacyValCol = j.Column
'get vars
legacyVal = j.Value
offTableVal = j.offset(0, -2).Value
offSystemFieldVal = j.offset(0, -1).Value
'find offSystemFieldVal in the column headers of MigrationExporter v2.3.xls (correct table/tab).
With wb4.Worksheets(offTableVal).Columns("A:" & endColLetter).Rows("1:1")
Set i = .Find(offSystemFieldVal, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)
'if it is found then..
If Not i Is Nothing Then
'where was it found
MEPasteCol = i.Column
MEPasteColLetter = ColNo2ColRef(MEPasteCol) 'get title
MEPasteColTitle = i.Value
Set cRange = wb2.Worksheets(ws2.Name).Rows("2:" & endRow).Columns(inputColLetter & ":" & inputColLetter)
Set pRange = wb4.Worksheets(offTableVal).Rows("2:" & endRow).Columns(MEPasteColLetter & ":" & MEPasteColLetter)
cRange.Copy
pRange.PasteSpecial Paste:=xlValues
End If
End With
Set j = .FindNext(j)
'loop while something is found, unless it was the first thing found
Loop While Not j Is Nothing And j.Address <> firstAddress
End If
End With

 

[SkipVought]

k,

Your j find loop workk just fine in my test, commenting out the superfluous code within the outer do loop. What happens if you do that?

You say that there IS another occurence of your find value. You are looking at xlWhole, so legacyVal & legacyValCol are unnecessry since these values are inputColTitle and 3 respectively, YES?

When you say, "on the second run of the do loop," do you mean 1) the second time within the loop or 2) the second time the loop is run?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Hi Skip,
You are right, I added legacyVal & legacyValCol just to try to work out what whas going wrong.
By "on the second run of the do loop," I mean the second time within the loop.
I have removed the code from within the loop and everything works ok. So the problem seems to be with the first find losing focus when doing another find within that loop. The only problem with this is I need the additional find.
Is there an easy way around this?
Thanks,
K

 

[PHV]

knifey, AFAIK you can't nest Find ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

put a break in your code on the outer LOOP statement. Step thru the second time thru the loop and observe what is happening using the Watch Window. faq707-4594

Just some additional comments concerning your code in general...

'search field lookup file to find the legacy column title
'[b]why not use the ws3 object?[/b]
'[b]assuming there are values in every row of data in col C[/b]
 With ws3.Range(ws3.Cells(1, "C"), ws3.Cells(1, "C").End(xlDown))
'[b]the FIND method defaults to xlWhole[/b]
     Set j = .Find(inputColTitle)
     'if it is found then..
     If Not j Is Nothing Then
         'where was it found
         firstAddress = j.Address
         'a continuous loop
         Do
             'what column?
             legacyValCol = j.Column
             'get vars
             legacyVal = j.Value
             offTableVal = j.Offset(0, -2).Value
             offSystemFieldVal = j.Offset(0, -1).Value
                 
             'do other stuff here
                 
             Set j = .FindNext(j)
         'loop while something is found, unless it was the first thing found
 '[b]you already test for the existence of j so why again?[/b]
        Loop While j.Address <> firstAddress
    End If
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Thanks PHV,
That would explain alot. I'll have to try it another way.
Cheers,
Knifey

 

[knifey]

Thanks Skip,
I'll try these code alterations out.
Cheers,
Roy

 

[PHV]

What about this ?

With ws3.Columns("C:C").Rows("1:" & endRowFieldLookup)
  Set j = .Find(inputColTitle, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
  'if it is found then..
  If Not j Is Nothing Then
    'where was it found
    firstAddress = j.Address
    'a continuous loop
    Do
      offTableVal = j.Offset(0, -2).Value
      offSystemFieldVal = j.Offset(0, -1).Value
      'find offSystemFieldVal in the column headers of MigrationExporter v2.3.xls (correct table/tab).
      With wb4.Worksheets(offTableVal)
        For MEPasteCol = 1 To .Columns(endColLetter).Column
          If .Cells(1, MEPasteCol) = offSystemFieldVal Then
            MEPasteColLetter = ColNo2ColRef(MEPasteCol)                                'get title
            MEPasteColTitle = offSystemFieldVal
            Set cRange = ws2.Rows("2:" & endRow).Columns(inputColLetter & ":" & inputColLetter)
            Set pRange = .Rows("2:" & endRow).Columns(MEPasteColLetter & ":" & MEPasteColLetter)
            cRange.Copy
            pRange.PasteSpecial Paste:=xlValues
            Exit For
          End If
        Next
      End With
      Set j = .FindNext(j)
      'loop while something is found, unless it was the first thing found
    Loop While Not j Is Nothing And j.Address <> firstAddress
  End If
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886