Changing a value of one cell from another

IrishinNYC

New Member
Joined
Feb 10, 2005
Messages
2
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]
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Feb 10, 2005
Messages
2
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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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 ( :rolleyes: )
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
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top