Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Function Program
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function Program
Please I need your help to adjust my program.
I have the following program (see below) that I want to make it generic. As
you will notice this program works for specific number of cells. However, my
new sheet has two hundred cells more and I want to avoid add more lines in my
program.
Do you think is possible to make it generic for numbers of cells at
anytime?...
I will really appreciate your helping me....

Maperalia
------------------------------------------------------------

Sub Calculation()

'Apply formula in each cell at Column "C"
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-3]C-RC[-1])/R[-3]C)*100)))"
Range("C9").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-4]C-RC[-1])/R[-4]C)*100)))"
Range("C10").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-5]C-RC[-1])/R[-5]C)*100)))"
Range("C11").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-6]C-RC[-1])/R[-6]C)*100)))"
Range("C12").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-7]C-RC[-1])/R[-7]C)*100)))"
Range("C13").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-8]C-RC[-1])/R[-8]C)*100)))"
Range("C14").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-9]C-RC[-1])/R[-9]C)*100)))"
Range("C8").Select

'Apply additional formula in each cell at Column "D"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=100-RC[-1]"
Range("D8").Select
Selection.AutoFill Destination:=Range("D8:D14"), Type:=xlFillDefault
Range("D8:D14").Select
Range("D8").Select
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Function Program
Sounds like you want

For i = 8 To Cells(Rows.Count,"C").End(xlUp).Row
Range("C"& i).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-" & i - 5 & "]C-RC[-1])/R[-" & i - 5 &
"]C)*100)))"
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"maperalia" wrote in message
...
Please I need your help to adjust my program.
I have the following program (see below) that I want to make it generic.
As
you will notice this program works for specific number of cells. However,
my
new sheet has two hundred cells more and I want to avoid add more lines in
my
program.
Do you think is possible to make it generic for numbers of cells at
anytime?...
I will really appreciate your helping me....

Maperalia
------------------------------------------------------------

Sub Calculation()

'Apply formula in each cell at Column "C"
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-3]C-RC[-1])/R[-3]C)*100)))"
Range("C9").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-4]C-RC[-1])/R[-4]C)*100)))"
Range("C10").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-5]C-RC[-1])/R[-5]C)*100)))"
Range("C11").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-6]C-RC[-1])/R[-6]C)*100)))"
Range("C12").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-7]C-RC[-1])/R[-7]C)*100)))"
Range("C13").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-8]C-RC[-1])/R[-8]C)*100)))"
Range("C14").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-9]C-RC[-1])/R[-9]C)*100)))"
Range("C8").Select

'Apply additional formula in each cell at Column "D"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=100-RC[-1]"
Range("D8").Select
Selection.AutoFill Destination:=Range("D8:D14"), Type:=xlFillDefault
Range("D8:D14").Select
Range("D8").Select
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default Function Program
Try:

Sub myCalc()
Dim eRow As Long
eRow = Cells(Rows.Count, 2).End(xlUp).Row
Range(Cells(8, 3), Cells(eRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(8, 4), Cells(eRow, 4)).FormulaR1C1 = "=100-RC[-1]"
End Sub

Hope this helps
Rowan

"maperalia" wrote:

Please I need your help to adjust my program.
I have the following program (see below) that I want to make it generic. As
you will notice this program works for specific number of cells. However, my
new sheet has two hundred cells more and I want to avoid add more lines in my
program.
Do you think is possible to make it generic for numbers of cells at
anytime?...
I will really appreciate your helping me....

Maperalia
------------------------------------------------------------

Sub Calculation()

'Apply formula in each cell at Column "C"
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-3]C-RC[-1])/R[-3]C)*100)))"
Range("C9").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-4]C-RC[-1])/R[-4]C)*100)))"
Range("C10").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-5]C-RC[-1])/R[-5]C)*100)))"
Range("C11").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-6]C-RC[-1])/R[-6]C)*100)))"
Range("C12").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-7]C-RC[-1])/R[-7]C)*100)))"
Range("C13").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-8]C-RC[-1])/R[-8]C)*100)))"
Range("C14").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-9]C-RC[-1])/R[-9]C)*100)))"
Range("C8").Select

'Apply additional formula in each cell at Column "D"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=100-RC[-1]"
Range("D8").Select
Selection.AutoFill Destination:=Range("D8:D14"), Type:=xlFillDefault
Range("D8:D14").Select
Range("D8").Select
End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function Program
Rowan;
Thank you very much!!!!!.... It is working perfectly!!!!!!!!!!.
However, I want to ask you for another favor. Since, this program works for
the entire column 3 and column 4 starting from row 8. I wonder if I can stop
it in the row 250 and then start with another calculation from row 305 using
the same column 3 and column 4. I need to do this because in the cell R300E5
I will use the different value used in R5C3.

I wrote the program (see below) base in your advise, however, I do not know
how to make the different in variables.

Thanks in advance and I really appreciate your taking your time to helping
me in this matter.

Best regards.
Maperalia

Option Explicit
Public Sub Calculation1()
Dim eRow As Long
eRow = Cells(Rows.Count, 2).End(xlUp).Row
Range(Cells(8, 3), Cells(eRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(8, 4), Cells(eRow, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub


Public Sub Calculation2()
Dim eRow As Long
eRow = Cells(Rows.Count, 2).End(xlUp).Row
Range(Cells(305, 3), Cells(eRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(305, 4), Cells(eRow, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub


Public Sub AllCalculations()
Calculation1
Calculation2
End Sub





"Rowan" wrote:

Try:

Sub myCalc()
Dim eRow As Long
eRow = Cells(Rows.Count, 2).End(xlUp).Row
Range(Cells(8, 3), Cells(eRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(8, 4), Cells(eRow, 4)).FormulaR1C1 = "=100-RC[-1]"
End Sub

Hope this helps
Rowan

"maperalia" wrote:

Please I need your help to adjust my program.
I have the following program (see below) that I want to make it generic. As
you will notice this program works for specific number of cells. However, my
new sheet has two hundred cells more and I want to avoid add more lines in my
program.
Do you think is possible to make it generic for numbers of cells at
anytime?...
I will really appreciate your helping me....

Maperalia
------------------------------------------------------------

Sub Calculation()

'Apply formula in each cell at Column "C"
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-3]C-RC[-1])/R[-3]C)*100)))"
Range("C9").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-4]C-RC[-1])/R[-4]C)*100)))"
Range("C10").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-5]C-RC[-1])/R[-5]C)*100)))"
Range("C11").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-6]C-RC[-1])/R[-6]C)*100)))"
Range("C12").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-7]C-RC[-1])/R[-7]C)*100)))"
Range("C13").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-8]C-RC[-1])/R[-8]C)*100)))"
Range("C14").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R[-9]C-RC[-1])/R[-9]C)*100)))"
Range("C8").Select

'Apply additional formula in each cell at Column "D"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=100-RC[-1]"
Range("D8").Select
Selection.AutoFill Destination:=Range("D8:D14"), Type:=xlFillDefault
Range("D8:D14").Select
Range("D8").Select
End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Run a sub or function on program start? RASelkirk[_3_] Excel Programming 1 November 16th 04 10:53 PM
Run a sub or function on program start? RASelkirk[_2_] Excel Programming 1 November 14th 04 01:58 PM
Run a sub or function on program start? RASelkirk Excel Programming 2 November 13th 04 11:37 PM
How to program a series sum function Joao[_2_] Excel Programming 2 June 18th 04 04:12 PM
program a function key John Sheakley Excel Programming 1 September 30th 03 02:31 AM


All times are GMT +1. The time now is 02:39 AM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement