FindNext

[Giri]

Hi Guys,

With the following code, I initially want to find the word "UNIT_ADJ" in one particular spreadsheet. I then want to extract the last word from the cell containing "UNIT_ADJ" and then insert this extracted word into another spreadsheet.

However at the moment, the commented line in the code is not finding the next cell containing "UNIT_ADJ". Rather, it is searching for the last extracted word from the cell containing "UNIT_ADJ"

When I remove the middle block of code,[VBA] Workbooks(LastWordDivbyBackslash(taextract_String)).Activate ' ACTIVATE TA_EXTRACT WORKBOOK
Set foundCell_TA = Range("A2:A" & finalRow).Find(fundName)
foundCell_TA.EntireRow.Insert Shift:=xlShiftDown
MsgBox foundCell_TA.Address
foundCell_TA.Offset(-1, 0).Value = fundName
foundCell_TA.Offset(-1, 5).Value = unitadj_value [/VBA]
the search seems to work correctly. For this reason, I think the problem may have to do with the fact that I have TWO Find methods and it is using the last Find Method in the subsequent Find.Next method.

Anyone know how to get around this?

Thanks for your help!

Kind Regards,

Giri

[VBA]

Set foundCell = Range("N2:N" & lastrow_Noms).Find("UNIT_ADJ")
If Not foundCell Is Nothing Then
firstfound_Address = foundCell.Address

End If

Do

fundName = LastWord(foundCell.Value)
unitadj_value = foundCell.Offset(0, -7).Value


Workbooks(LastWordDivbyBackslash(taextract_String)).Activate ' ACTIVATE TA_EXTRACT WORKBOOK
Set foundCell_TA = Range("A2:A" & finalRow).Find(fundName)
foundCell_TA.EntireRow.Insert Shift:=xlShiftDown
MsgBox foundCell_TA.Address
foundCell_TA.Offset(-1, 0).Value = fundName
foundCell_TA.Offset(-1, 5).Value = unitadj_value


Workbooks(LastWordDivbyBackslash(nomsnetoff_String)).Activate ' ACTIVATE NOMSNETOFF WORKBOOK
Set foundCell = Range("N2:N" & lastrow_Noms).FindNext(foundCell) ' ********** NOT FINDING THE NEXT CELL CONTAINING "UNIT_ADJ" ***********

Loop While Not foundCell Is Nothing And foundCell.Address <> firstfound_Address

[/VBA]

[Teeroy]

Hi Giri,

There are some arguments of the .FIND method that are saved between calls. Have a look at the page below which gives some background and lists some of the arguments that it recommends be stated explicitly.

http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

Also I believe the .FINDNEXT method needs to use the same Range as the preceding .FIND.

[Giri]

Hi Teeroy,

I tried out your suggestion but I'm having the same issue. I've decided to approach this thing in another way.

Thanks for your help anyway!

Kind Regards,

Giri

[Aflatoon]

if you use another Find call in between, you can't use FindNext - you have to use Find again in the outer loop specifying the same arguments other than finding after the last found cell.