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++;
        }
    }
}

[download]
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"});

[download]
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.

In reply to FindNext function in OLE Excel by esr

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.