ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Function Program (https://www.excelbanter.com/excel-programming/338261-function-program.html)

maperalia
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

Bob Phillips[_6_]
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

Rowan[_4_]
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

maperalia
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


All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com