Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 16th, 2006, 09:34 AM
Registered User
 
Join Date: Jan 2006
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


 
Old January 16th, 2006, 09:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 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
The Following User Says Thank You to maccas For This Useful Post:
adayne (July 29th, 2016)
 
Old January 16th, 2006, 10:08 AM
Registered User
 
Join Date: Jan 2006
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


 
Old January 16th, 2006, 10:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 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


 
Old January 17th, 2006, 02:25 AM
Registered User
 
Join Date: Jan 2006
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.





 
Old January 17th, 2006, 04:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 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

 
Old November 29th, 2012, 12:51 AM
Registered User
 
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
 
Old November 29th, 2012, 07:39 PM
Registered User
 
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
 
Old July 29th, 2016, 12:35 AM
Registered User
 
Join Date: Jul 2016
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
Default Method range of object worksheet failed

I used the code suggested earlier
Sub UpdateVal()
ActiveSheet.Range("AK10").Value = ActiveSheet.Range("AM10").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
UpdateVal
End Sub
and it works but it keeps giving a Method range of object worksheet failed error, can you help. I need to copy and paste a cell value not the code onto another program that wont allow me to paste just the value, so I need the value moved to another cell and I need it to update when the cell changes.
Thank you in advance for your help





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 03:36 AM
Cell returns its own formula after a macro! timoma Excel VBA 2 December 19th, 2004 03:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.