

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 .





January 16th, 2006, 08:34 AM

Registered User


Join Date: Jan 2006
Location: , , .
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, 08:55 AM

Friend of Wrox


Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 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

January 16th, 2006, 09:08 AM

Registered User


Join Date: Jan 2006
Location: , , .
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, 09:23 AM

Friend of Wrox


Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 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, 01:25 AM

Registered User


Join Date: Jan 2006
Location: , , .
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, 03:26 AM

Friend of Wrox


Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 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 28th, 2012, 11:51 PM

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, 06: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

Thread Tools 

Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off




