Anthony's VBA Forum Index Anthony's VBA Forum
Where the knowledge is shared
Menu
 Anthony's VBA Forum IndexHome Page
 Anthony's VBA Forum IndexForum Index
FAQFAQ
MemberlistMemberlist
UsergroupsUsergroups
RegisterRegister
ProfileProfile
Log in to check your private messagesMessages
Log inLogin/Out

Quick Search

Advanced Search

Links
Consulting
Products

Who's Online
[ Administrator ]
[ Moderator ]


copy a formula from one cell to anothe using any excel funct

 
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA
View previous topic :: View next topic  
Author Message
ajay



Joined: 23 Aug 2008
Posts: 8

PostPosted: Wed Aug 27, 2008 1:20 am    Post subject: copy a formula from one cell to anothe using any excel funct Reply with quote

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
View user's profile Send private message
mohan.ankur
Moderator
Moderator


Joined: 02 Aug 2006
Posts: 52

PostPosted: Mon Nov 17, 2008 3:00 pm    Post subject: Reply with quote

To Copy a formula

You can use the Formula property of range object.

Something like

Range("A1").Formula=Range("A2").formula

will get A2s formula in A1

Or try recording copy PasteSpecial Formulas to get the code....
_________________
http://www.exceldepot.com
http://www.xlmacros.com
http://www.learnexcel.org
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
ajay



Joined: 23 Aug 2008
Posts: 8

PostPosted: Wed Dec 03, 2008 5:55 am    Post subject: Thanks Reply with quote

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
View user's profile Send private message
ajay



Joined: 23 Aug 2008
Posts: 8

PostPosted: Wed Dec 03, 2008 7:29 am    Post subject: Reply with quote

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
View user's profile Send private message
Kenneth Hobson
Gold Member
Gold Member


Joined: 25 Mar 2007
Posts: 21
Location: Tecumseh, OK

PostPosted: Fri Dec 05, 2008 3:51 pm    Post subject: Reply with quote

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
View user's profile Send private message
ajay



Joined: 23 Aug 2008
Posts: 8

PostPosted: Sat Dec 13, 2008 11:10 am    Post subject: Thanks Mr. Kenith. I will try it. Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
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