Changing a value of one cell from another

[IrishinNYC]

Ok Im sure this one is easy but I just cant get it.

I want to change the value in cell C1 to the value of cell A1 using a formula in cell B1. Cell C1 has to end up as a number.

So if cell A1 holds 123 as a value then the formula in B1 should change cell C1 to be the same as A1.

Alternitvely if there was a way to copy the value a formula produces this would suffice.

Example: if cell C1 held the formula =A1 is there a way to copy and paste the value the formula in C1 produces.

[/code]

 

[HalfAce]

Hello & welcome to the board.
The answers to your questions are, No and Yes.

No, a formula in B1 can not change the value in C1 (or any other cell).

Yes, you can copy the formula result in C1 and use PasteSpecial > Values to convert that from a formula to a static value.

If you are looking for a VBA solution to doing this, the code would look like this:
C1.Value = C1.Value or, you could do without the formula in C1 altogether and simply code it as:
C1.Value = A1.Value (even if A1 is the return of a formula.)

Hope this helps,
Dan

 

[IrishinNYC]

Fantastic.

Paste Special works perfect.

VBA a mystery (sort of) to me.

How would i insert that into the sheet and what if i had a whole sheet of values to do like (d1:f3).Value=(a1:c3).Value

 

[daniels012]

Sub PasteOnlyValue()

Selection.CurrentCell
Selection.Copy
Range.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

This should do what you want. Make sure you select a cell in Column A and the data will be copied to Column B. if you really need the copied item in column C then change the offset to (0,2)

 

[HalfAce]

If I understand your question, you don't insert that into the sheet. That code would go into a code module and then you call it by some method (as in clicking a button).

If you're interested in using a button, then you can do this:
1. From the Forms toolbar, choose the button of the button ( )
and draw it out onto your sheet.
2. In the dilogbox that opens up, In the field "Macro name" enter a name for your macro. (Let's call it CopyValues)
3. Click the button on the right that says New. (You are now in the VB Editor, in a standard code module.)
4. Copy this code and paste in the code module between the lines that say
Sub CopyValues()
(and)
End Sub

[D1:F3].Value = [A1:C3].Value

5. Press AltQ to close the editor and get back to the sheet. Now when you click the button, the formula returns in A1:C3 will show up as static values in D1:F3.

Hope it helps,
Dan