|
Anthony's VBA Forum Where the knowledge is shared |
|
|
View previous topic :: View next topic |
Author |
Message |
ajay
Joined: 23 Aug 2008 Posts: 8
|
Posted: Wed Aug 27, 2008 1:20 am Post subject: copy a formula from one cell to anothe using any excel funct |
|
|
hi !
I posted a problem but have received no replies so i have decided to break it up in parts. maybe i can hope to get some ideas.
So the 1st part of the simplified problem is this:
I have a formula in cell A1 which is SUM(c1:c5)
c1:c5 contain numbers from say 1,2,3,4,5 respectively or any data cos this is irrelevant.
I want that by some method i be able to copy the formula , not the value, in cell A1 i.e. to say cell B10 using some excel funtion. I have tried indirect but have not found a method of doing so.
hope this is simple enuff for some to answer.
Best Wishes
Ajay |
|
Back to top |
|
|
mohan.ankur Moderator
Joined: 02 Aug 2006 Posts: 52
|
|
Back to top |
|
|
ajay
Joined: 23 Aug 2008 Posts: 8
|
Posted: Wed Dec 03, 2008 5:55 am Post subject: Thanks |
|
|
Dear Ankur,
Thanks for the reply. I will check out if it solves my purpose.
Mean while I have another problem.
If i have a list as below
Category Marks Max
a1 16
a1 75
a1 47
a1 54
b1 76
b1 38
b1 97
c1 85
c1 88
c1 64
c1 72
c1 59
from the ablove list is there a manner by which we can use a formula to find the greatest in each category without having to manually specify the range for each category. somehow the formula should itself generate the range for each category.
Is this possible. I would be grateful for any help.
Thanks and Regards,
Ajay Sehgal. |
|
Back to top |
|
|
ajay
Joined: 23 Aug 2008 Posts: 8
|
Posted: Wed Dec 03, 2008 7:29 am Post subject: |
|
|
hi ankur
I cannot understand what you offered in your previous mail regarding coping formulas by themselves and not the values.
I tried your :
Range("A1").Formula=Range("A2").formula
but i am unable to get in to work.
Can u give an example for me where u demostrate the above equation to copy formulas?
Thanks and Regards,
Ajay |
|
Back to top |
|
|
Kenneth Hobson Gold Member
Joined: 25 Mar 2007 Posts: 21 Location: Tecumseh, OK
|
Posted: Fri Dec 05, 2008 3:51 pm Post subject: |
|
|
Insert this into a Module and play from an empty sheet.
[code]Sub CopyFormula()
Dim i As Integer
'Fill C1:C5 with integers
For i = 1 To 5
Range("C" & i).Value = i
Next i
'Formula for A1
Range("A1").Formula = "=Sum(C1:C5)"
'Copy formula from A1 to B1
Range("B1").Formula = Range("A1").Formula
'Show values of A1 and B1
MsgBox "A1 = " & Range("A1").Value & vbCrLf & _
"B1 = " & Range("B1").Value
'Show Formula for A1 and B1
MsgBox "A1.Formula = " & Range("A1").Formula & vbCrLf & _
"B1.Formula = " & Range("B1").Formula
End Sub[/code] |
|
Back to top |
|
|
ajay
Joined: 23 Aug 2008 Posts: 8
|
Posted: Sat Dec 13, 2008 11:10 am Post subject: Thanks Mr. Kenith. I will try it. |
|
|
Here is another problem that i am facing. Any way around this one ?
If i have a list as below
Category Marks Max
a1 16
a1 75
a1 47
a1 54
b1 76
b1 38
b1 97
c1 85
c1 88
c1 64
c1 72
c1 59
from the ablove list is there a manner by which we can use a formula to find the greatest in each category without having to manually specify the range for each category. somehow the formula should itself generate the range for each category.
Is this possible. I would be grateful for any help.
Thanks and Regards, |
|
Back to top |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2002 phpBB Group
|