Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 26th 09, 06:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 27
Default Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing

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
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
Copy text from cell to cell with one cell changing text Bobby Excel Worksheet Functions 5 March 15th 07 11:09 PM
How do I change the width a specific cell w/o changing whole page THBCFLO Excel Discussion (Misc queries) 3 February 7th 06 11:01 PM
how to color code a row of cells based on a specific cell value Parker1333 New Users to Excel 1 February 2nd 05 08:01 AM


All times are GMT +1. The time now is 12:20 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017