Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options

Hi All,

I have a button whose job is to reset the value of a given range of
cells and then fill it with a formulae.

I know that if a range say value.001 is from B5 to B11 I can just
write the code:

Dim rng as Range

Set rng=Thisworkbook.Names("value.001").ReferstoRange
rng.Cells(1,1).Formulae="IF(Sheet1!AR66=0,0,($M$2* $B5)*Sheet1!AR66)"
rng.FillDown

but problem here is that value.001 is from B5 to Z11

and I need to copy these formulae across and down also.

Across the formulaes keeps changing like for C5 the formulae is =IF
(Sheet1!AS66=0,0,($M$2*$B5)*Sheet1!AS66)"

and down like for B6 it is

=IF(Sheet1!AR67=0,0,($M$2*$B6)*Sheet1!AR67)

Now rng.Filldown would have increment the numbers of AR67 to AR68 and
so on.

The excel has function of fillright, filldown and fillup. But if I
want a function or code that can do what i want across and down for
all the cells in a range.

Please guide,

Regards,

Yuvraj
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Yuvraj Excel Discussion (Misc queries) 0 June 26th 09 06:01 PM
How do I use formulae to display a specific picture in a cell? Tricia Excel Discussion (Misc queries) 1 June 19th 08 10:50 PM
Use a code to populate a cell with a specific value braveT Excel Worksheet Functions 5 April 13th 07 10:02 PM
Set default in auto fill options, I always want to copy cell, som. OSHAman Excel Discussion (Misc queries) 2 March 26th 05 12:22 AM
how to color code a row of cells based on a specific cell value Parker1333 New Users to Excel 1 February 2nd 05 09:01 AM


All times are GMT +1. The time now is 09:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"