http://www.perlmonks.org?node_id=791943

esr has asked for the wisdom of the Perl Monks concerning the following question:

I need to search through an Excel spreadsheet to find cells which contain long names in order to reduce the font size in those cells. I can do this using Find but I have been unable to get FindNext to work when trying to continue after the first one. Here is the code that works.

foreach $longname (@longnames) { $findit = $sheet->Range("A$first_data_row:$last_col$last_row")->Fi +nd({What=>$longname}); if ($findit) { $findit->Activate; $firstone = $excel->ActiveCell->{Address}; $fontsize = $excel->ActiveCell->Font->{Size}; $fontsize -= 2; $thisone = ""; $cnt = 0; while (($thisone ne $firstone) && ($cnt < 25)) { $thisone = $firstone if ($thisone eq ""); $newhash = $sheet->Range("$thisone")->Find({What=>$longnam +e}); $newhash->Activate; $thisone = $excel->ActiveCell->{Address}; $excel->ActiveCell->Font->{Size}=$fontsize; $cnt++; } } }
The $cnt stuff was inserted to prevent an infinite loop while I was debugging and is probably no longer needed.

Here is a small subset of some of the things I tried using FindNext instead of the Find in the while loop:

$newhash = $sheet->Cells->FindNext({After=>ActiveCell}); $newhash = $sheet->Cells->FindNext(); $newhash = $excel->Cells->FindNext({After=>ActiveCell}); $newhash = $sheet->Cells->FindNext({After=>"$thisone"});
I also tried using Range instead of Cells but I never found a combination that worked. All returned a null reference.

The code above works and I have no problem using it but I would like to know what I am doing wrong with FindNext.