Giri
05-18-2012, 11:17 PM
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, 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
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
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
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, 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
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
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