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 .

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 .

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

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 .

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.

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.

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.

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