Hi imrags,

I have not benchmarked, but by adding next we can avoid some extra executions. It is not going to boost more performance, but slight improvement will be there.

use strict;
use warnings;

for my $i (1 .. $LastRow)
{    
    if ($Sheet->Range("E$i")->{Value} =~ /MSD/){
                
        next;
        
    }
    else{

            $CB_COUNT++;
        
            if ( $Sheet->Range("J$i")->{Value} !~ /No_/i){
                             
                    $NM_COUNT++;
                        
            }                    
                            
    }
[download]

OP has updated the code. Posted solution for the previous one.
update: Removed extra 'if' statement in 'else' part.

You'll get a little extra benefit if you avoid entering a BLOCK, so:

  for my $i (1 .. $LastRow) {
    next if ($Sheet->Range("E$i")->{Value} =~ /MSD/) ;
    $CB_COUNT++;

    next if ($Sheet->Range("J$i")->{Value} =~ /No_/i) ;
    $NM_COUNT++;
  }
[download]

but as noted elsewhere, the major time cost is probably in the OLE calls.

Is it possible to pull a range into an array ? I'm thinking: @stuff = $Sheet->Range("J1:J$lastrow") ?

for $i (1 .. $LastRow)
{
    my $rango5 = "E".$i;
    my $rango6 = "J".$i;
    my $value5 = $Sheet->Range("$rango5")->{Value};
    my $value6 = $Sheet->Range("$rango5")->{Value};

    if ( $value5 !~ /MSD/)
    {
        $CB_COUNT++;
    }
    if ($value5 !~ /MSD/ && $value6 !~ /No_/i)
    {
        $NM_COUNT++;
    }
}
[download]

If all else fails and you're really desperate, you can also export the Excel file to a .csv file and then search through the CSV in Perl. You can then in parallel update your Excel file. That way, you save a lot of calls and data transfers through the OLE layer, but you add some complexity to your code. I'd use Text::CSV, as that one should even treat embedded newlines in your Excel data well enough.

Beware, when exporting (using Excel) .XLS to .CSV, that Excel (in its infinite wisdom) can return multiple individual cells for a single cell containing large amounts of data (AFAIR, the limit appeared to be ~ 1kB) - a situation that breaks both Text::CSV & Text::xSV since an unexpectedly higher number of columns appear to be returned from rows containing such cells.
A user level that continues to overstate my experience :-))

Alternatively, you could try using the Excel worksheet CountIf() function. Here is a small working example:

    #!/usr/bin/perl

    use strict;
    use warnings;

    use Win32::OLE;
    use Win32::OLE::Const 'Microsoft Excel';

    my $application = Win32::OLE->new("Excel.Application");
    my $workbook    = $application->Workbooks->Open("c:/temp/test.xls"
+);
    my $worksheet   = $workbook->Worksheets(1);
    my $range       = $worksheet->Range("A1:A8");

    my $count = $application->WorksheetFunction->CountIf( $range, "2" 
+);

    print $count, "\n";

    __END__
[download]

As a further alternative you could try using Jon Allen's XLSperl which gives you a commandline application that behaves like perl but works on Excel files. Something like this:

   XLSperl -lne 'print if /pattern/ and $COL eq "A"' file.xls
[download]

XLSperl used Spreadsheet::ParseExcel which may also be an alternative solution.

* This isn't a general rule. It is usually better if you can call a single method on a Range rather than iterate through the Cells. However, I don't think there is an applicable method that will produce the desired results in this case.

--
John.

Unfortunately the countif() function of excel doesn't do what I want it to
I have also tried sumproduct for that...
But sadly it doesn't work as well...that's the reason i'd to resort to loops...
Raghu