Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 16th, 2006, 08:34 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel putting result of a formula in another Cell

Hi Everyone,

I am having a formula in Cell B5 and I want to put the result of this formula in Cell A5. Please note that I dont want a formula in cell A5. Cell A5 should contain the result of formula written in B5. How do i do this. Then I want to copy the same formula down to other cells .


Please help urgently.

Thanks
Hakim


Reply With Quote
  #2 (permalink)  
Old January 16th, 2006, 08:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 Posts
Default

I'm not quite sure I understand the motivation for the specific functionality you are requesting. However, if I understand your request, you'd like to hard-code the value result of a formula in one cell into another cell. The only way you can do this (other than by by writing out by hand) is to run a VBA subroutine. Something like this should do the trick (replace "A5" and "B5 for different cells as required)
Code:
Sub UpdateVal()
    ActiveSheet.Range("A5").Value = ActiveSheet.Range("B5").Value
End Sub
Clearly each time the formula in the first cell eveluates to a different result the hard-coded value in the second cell would go "out of date". If you'd like a dynamicly updating hard-coded cell you'll need to use a worksheet event along the lines of:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateVal
End Sub
Reply With Quote
  #3 (permalink)  
Old January 16th, 2006, 09:08 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks For replying
I will explain you my problem once again I want: when i type in B5 Cell :
               =if ($a$2="yes", A5="Work Done") ... something of this sort meaning if a certain condition evaluates to true then write the result as work done in A5. Result should not be written in B5. and this particular formula is copied right down the excel sheet. meaning B6 will have formula ($a$2="yes",A6="work done") .

How do i do this? Please help urgently

Quote:
quote:Originally posted by hakimk
 Hi Everyone,

I am having a formula in Cell B5 and I want to put the result of this formula in Cell A5. Please note that I dont want a formula in cell A5. Cell A5 should contain the result of formula written in B5. How do i do this. Then I want to copy the same formula down to other cells .


Please help urgently.

Thanks
Hakim


Reply With Quote
  #4 (permalink)  
Old January 16th, 2006, 09:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 Posts
Default

In that case you cannot do specifically what you want with Excel sheet functions alone.

Why, out of interest, are you averse to having the string stored as the result of a formula as opposed to a hard-coded value in a cell? If you refer to a cell containing a formula and test on its value the result of the formula is what is passed and not the formula itself so it should be identical.

In any case, my previous exaple should acheive what you want to do. Just type in cell B5:

=IF($A$2="yes","Work Done,"")

Run the UpdateVal macro (Alt + F8) and the hard-coded string "Work Done" will be in cell A5.

Alternatively, if you are shy of macros, you could run the marginally more manual method of copying cell B5 and then Paste Special as values the result onto cell A5.

Maccas


Reply With Quote
  #5 (permalink)  
Old January 17th, 2006, 01:25 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, Thanks once again, Yes it works fine. But I guess its my mistake i didnt explain my problem correctly. So i will give u details again :

In the cell A3 : I am putting some Text Let's say Hakim
In the cell B4: I am putting a formula which says if the value in A3="Hakim" then put this value in B3 and not B4 (This can be done using your macro). And now this value in B3 should remain Hakim and not change even if the value in A3 changes.
So what I want is whatever is the value inserted in the A4 Column by B4 should remain as it is.

With the code you gave me it transfers the value from B3 to B4 but whenever B3 changes B4 also changes this shouldnt happen.





Reply With Quote
  #6 (permalink)  
Old January 17th, 2006, 03:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 Posts
Default

In that case write a row / column of normal Excel formulae. Clearly these values will change as precedant cells change.

To lock the values in these cells:
1) Select the row / column of formulae cells
2) Copy (Press Ctrl + c)
3) Select a blank cell in the adjacent row / column
4) Paste Special, Values (Edit -> Paste Special...) - this will create a row / column of hard-coded outputs of the row / column of formulae at the time you did this.

If you like, you can even hard-code over the formulae them sleves so the cell never move again.

Maccas

Reply With Quote
  #7 (permalink)  
Old November 28th, 2012, 11:51 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Continued help

i am wondering if there is a way to continue the code i have here

Private Sub CommandButton1_Click()
ActiveSheet.Range("E7").Value = ActiveSheet.Range("B7").Value
ActiveSheet.Range("E8").Value = ActiveSheet.Range("B8").Value
ActiveSheet.Range("E9").Value = ActiveSheet.Range("B9").Value
ActiveSheet.Range("E10").Value = ActiveSheet.Range("B10").Value
ActiveSheet.Range("E11").Value = ActiveSheet.Range("B11").Value
ActiveSheet.Range("E12").Value = ActiveSheet.Range("B12").Value
ActiveSheet.Range("E13").Value = ActiveSheet.Range("B13").Value
ActiveSheet.Range("E14").Value = ActiveSheet.Range("B14").Value
ActiveSheet.Range("E15").Value = ActiveSheet.Range("B15").Value
ActiveSheet.Range("E16").Value = ActiveSheet.Range("B16").Value
ActiveSheet.Range("E17").Value = ActiveSheet.Range("B17").Value
ActiveSheet.Range("E18").Value = ActiveSheet.Range("B18").Value
ActiveSheet.Range("E19").Value = ActiveSheet.Range("B19").Value
ActiveSheet.Range("E20").Value = ActiveSheet.Range("B20").Value
ActiveSheet.Range("E21").Value = ActiveSheet.Range("B21").Value
End Sub

until say ActiveSheet.Range("E10000").Value = ActiveSheet.Range("B10000").Value

without manualy typing it all
Reply With Quote
  #8 (permalink)  
Old November 29th, 2012, 06:39 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try:

Code:
Private Sub CommandButton1_Click()

Dim i as integer

For i = 7 to 10000
ActiveSheet.Range("E" & i).Value = ActiveSheet.Range("B" & i).Value
next i

end sub
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Putting Formula in Excel Sheet ritesh2381 ASP.NET 1.0 and 1.1 Basics 0 March 26th, 2008 05:59 AM
Using Formula on Active Cell manojkumarsoni Excel VBA 3 August 26th, 2007 08:56 AM
Strange Problem - Missing of Formula Field Result r_taduri Crystal Reports 0 July 9th, 2006 05:51 AM
What would the formula "=+D4" do in a cell? BrianWren Excel VBA 2 February 2nd, 2006 02:36 AM
Cell returns its own formula after a macro! timoma Excel VBA 2 December 19th, 2004 02:48 PM



All times are GMT -4. The time now is 05:11 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.