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
Could you please help me with the following.
I have a program that 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.

The program below will show you what I am taking about,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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Function program
Is this what you want

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(eRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(eRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub



--

HTH

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


"maperalia" wrote in message
...
Could you please help me with the following.
I have a program that 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.

The program below will show you what I am taking about,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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,718
Default Function program
If sub Calculate1 puts the formula in like this it will affect rows 2
through 250 only rather than going to the last row of data in column 2:

Range(Cells(8, 3), Cells(250, 3)).FormulaR1C1 = .....


--
Jim
"maperalia" wrote in message
...
Could you please help me with the following.
I have a program that 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.

The program below will show you what I am taking about,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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Function program

How about change to erow=250 in calculation1? If you us
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last row

--
hidek
-----------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890
View this thread: http://www.excelforum.com/showthread.php?threadid=39948

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function program
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error1004
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last row.


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=399482


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Function program
That looks like mine, I missed a variable. Try this

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub


--

HTH

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


"maperalia" wrote in message
...
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error'1004'
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last row.


--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399482




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function program
Bob;
Thanks for your quick respond.
However, the program youjust sent me is exactly the previous one. IO do not
see the changes.
Could please please verify it .

Thanks.
Maperalia



"Bob Phillips" wrote:

That looks like mine, I missed a variable. Try this

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub


--

HTH

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


"maperalia" wrote in message
...
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error'1004'
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last row.


--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399482





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function program
Bob;
Thanks for your quick respond.
However, the program youjust sent me is exactly the previous one. IO do not
see the changes.
Could please please verify it .

Thanks.
Maperalia

"Bob Phillips" wrote:

That looks like mine, I missed a variable. Try this

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub


--

HTH

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


"maperalia" wrote in message
...
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error'1004'
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last row.


--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399482





Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function program
Bob;
Thanks for your quick respond.
However, the program youjust sent me is exactly the previous one. I do not
see the changes.
Could please please verify it .

Thanks.
Maperalia



"Bob Phillips" wrote:

That looks like mine, I missed a variable. Try this

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub


--

HTH

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


"maperalia" wrote in message
...
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error'1004'
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last row.


--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399482





Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Function program
It is slightly different

I changed

Range(Cells(startRow, 4), Cells(eRow, 4)).FormulaR1C1 = _

to this

Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _

in 2 places

--

HTH

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


"maperalia" wrote in message
...
Bob;
Thanks for your quick respond.
However, the program youjust sent me is exactly the previous one. I do not
see the changes.
Could please please verify it .

Thanks.
Maperalia



"Bob Phillips" wrote:

That looks like mine, I missed a variable. Try this

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub


--

HTH

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


"maperalia" wrote in message
...
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error'1004'
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last
row.


--
hideki

------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399482







Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Function program
Bob;
Thanks again for your quick respond.
I sorry to bother again but I ran the program with the changes you made and
I still have gotten the same error message I mentioned in my previous e-mail.

Could you please tell me how can be fixed it?
I will really apprciate it.

Regards.
Maperalia


"Bob Phillips" wrote:

It is slightly different

I changed

Range(Cells(startRow, 4), Cells(eRow, 4)).FormulaR1C1 = _

to this

Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _

in 2 places

--

HTH

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


"maperalia" wrote in message
...
Bob;
Thanks for your quick respond.
However, the program youjust sent me is exactly the previous one. I do not
see the changes.
Could please please verify it .

Thanks.
Maperalia



"Bob Phillips" wrote:

That looks like mine, I missed a variable. Try this

Option Explicit

Public Sub Calculation1(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub Calculation2(startRow, endRow)
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 = _
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"
Range(Cells(startRow, 4), Cells(endRow, 4)).FormulaR1C1 = _
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).row
End Sub


--

HTH

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


"maperalia" wrote in message
...
Gentlemen;
I really appreciate your giving this information.
However, when I run the program I got this window message:
Run-time error'1004'
Application-defined or object-defined error
After I click debug it is highlighting at:
Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((( R300E5-RC[-1])/ R300E5)*100)))"

Thanks in advance
Maperalia

The program is:
Option Explicit

Public Sub Calculation1(startRow, endRow)

Range(Cells(startRow, 3), Cells(endRow, 3)).FormulaR1C1 =
"=IF(RC[-1]=0,"""",(100-(((R5C3-RC[-1])/R5C3)*100)))"
Range(Cells(startRow, 4), Cells(250, 4)).FormulaR1C1 =
"=IF(RC[-2]=0,"""",100-RC[-1])"
End Sub

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

Public Sub AllCalculations()
Calculation1 8, 250
Calculation2 305, Cells(Rows.Count, 2).End(xlUp).Row
End Sub









"hideki" wrote:


How about change to erow=250 in calculation1? If you use
erow=Cells(Rows.Count, 2).End(xlUp).Row it will go until the last
row.


--
hideki

------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=399482








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
Function Program maperalia Excel Programming 3 August 25th 05 05:51 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 12:37 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