
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



January 16th, 2006, 09:34 AM

Registered User


Join Date: Jan 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts


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

January 16th, 2006, 09:55 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


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 hardcode 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 hardcoded value in the second cell would go "out of date". If you'd like a dynamicly updating hardcoded 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:


January 16th, 2006, 10:08 AM

Registered User


Join Date: Jan 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts


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


January 16th, 2006, 10:23 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


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 hardcoded 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 hardcoded 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

January 17th, 2006, 02:25 AM

Registered User


Join Date: Jan 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts


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.

January 17th, 2006, 04:26 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


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 hardcoded outputs of the row / column of formulae at the time you did this.
If you like, you can even hardcode over the formulae them sleves so the cell never move again.
Maccas

November 29th, 2012, 12:51 AM

Registered User


Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts


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

November 29th, 2012, 07:39 PM

Registered User


Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts


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

July 29th, 2016, 12:35 AM

Registered User


Join Date: Jul 2016
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts


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


