$Formula1= $Sheet->Range('A499')->{Formula};
$new_row = 500;
$Formula1 =~ s/\d+/$new_row/g;
$Sheet->Range('A'.$new_row)->{Formula} = $Formula1;
[download]

Thanks for the solution. But I am looking for a more generic solution as I have around 50 different formulas to be substituted with new row and they are spread into different columns . eg.

 
=IF(C489=0,0,(ABS(C492-C489)/C489))
[download]

in the above formula above substitution $Formula1 =~ s/\d+/$new_row/g; will not work.

If you want the behaviour of the copy & paste mechanism, you should try to use that: select the cells, copy them, move the cursor to another cell, paste there.

I'm not familiar with OLE at all, but I'm quite sure that this has to work somehow, and I even suspect you'll find some code examples if you search hard enough.

All you need to do is increment the number for the row you want to copy to.

my $Formula = $Sheet->Range("A499")->{Formula};

# The next 10 rows
print "=MAX(B$_:AR$_)" for 500..510;
[download]

Obviously, you'd change the print statement into whatever method you need to invoke to paste your formula.

TIMTOWTDI (a very linear way without using the native abilities of a module (possibly use Win32::OLE::Const 'Microsoft Excel'; or similar):

#!usr/perl/bin
use strict;
use warnings;

my $Formula = "=MAX(B499:AR499)";
#desired: "=MAX(B500:AR500)"

my ($formula_begin, $therest) = split /\({1}/, $Formula;  # ESCAPE the
+ open paren

my ($cell1, $cell2) = split /:/, $Formula;

my ($cell1a, $cell1b) = split /\(/, $cell1;

my ($cell2a, $closeparen) = split /\)/,$cell2; # One way to get rid of
+ the trailing ')' so we can increment the number, later.

print "First cell referenced: $cell1b, second cell referenced: $cell2a
+\n\n";

$cell1b =~ /([A-Z]+)(\d+)/;
my $cell1balpha = $1; my $cell1bdigits = $2;

$cell2a =~ /([A-Z]+)(\d+)/;
my $cell2aalpha = $1; my $cell2adigits = $2;

++$cell1bdigits;  
++$cell2adigits;

print "AFTER INCREMENT, first cell referenced: $cell1balpha" . $cell1b
+digits . "; second cell referenced: $cell2aalpha" ."$cell2adigits\n\n
+";

print "\tRefs adjusted formula: $formula_begin" . "($cell1balpha" . $c
+ell1bdigits. ":$cell2aalpha" ."$cell2adigits" . ")\n";
[download]

Output: Refs adjusted formula: =MAX(B500:AR500)

This is not the recommended way but may serve to illustrate one way of dealing with the likes of cell references in OO's scalc or Excel.

Added plaudits to oko1, missing colon in last code item, output, and the missing "in" in last paragraph

Also see Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks) for whenever you see things that Excel lets you do manually but where you encounter problems reproducing them in code.