Function to copy a value from a formula in cell C3 to D3.

Sbubendorf

New Member
Joined
Aug 8, 2007
Messages
8
I know how to copy and paste special as a value, but if possible, I would like to insert a function to do this into D3. That will allow me to autofill all of row 3 to the rows below, and repeat the process from project to project.

I would think this should be pretty basic, but I simply can't figure out how to do it. Thank you for any help that can be provided !!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
im not sure exactly what you're looking for?

you could create a custom menu button then assign it to a macro like this:

Code:
Sub Macro1()
    Rows("3:3").Select
    With Selection
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells(1, 1).Select
    End With
    
End Sub

that will paste special values in all of row 3 every time you hit the button

or if you can give more details to what you want exactly
 
Upvote 0
I agree with that, and I'd even add a keyboard shortcut to the custom button. At any rate put it in the Personal workbook to use it from project to project, and put it on a custom icon on a toolbar so it's always there.

You could also avoid selecting cells by using a Range variable and .Offset(1,0).Resize(numRows) for the destination.
 
Upvote 0
Thanks, guys !!

I think what you've posted will help considerably. To clarify, in case there are better options once clarified:

I have data in column A (variable number of rows). I have functions in cells B3 and C3. I have a function in cell E3 that utilizes the info in cell D3. I was hoping to insert a defined function in cell D3 that accomplishes what the posted macro is designed to do: copy values only from cell C3.

(I see in the preview that my "columns" below are going to get lost in the forum, but I think the intent will be evident:)

Before Sorting on Column D:

A B C D E

BA33.DXF BA33 33BA 33BA BA33
BA34.DXF BA34 34BA 34BA BA34
BB33.DXF BB33 33BB 33BB BB33

After Sorting on Column D:

A B C D E

BA33.DXF BA33 33BA 33BA BA33
BA34.DXF BA34 34BA 33BB BB33
BB33.DXF BB33 33BB 34BA BA34


Column A are filenames pasted from clipboard, typically 500 rows, for example.
Column B has the extension stripped from the filenames.
Column C inverts the filename which is actually a part name.
Column D should be the value obtained from the formula in column C.
Column E is the part name put back together correctly.
Columns B & E are the sorted data that I will work with.
Columns C & D could actually be eliminated after B & E are obtained, but I prefer to see the intermediate steps.

I use the spreadsheet, among other things, to sort column D, which produces Column E sorted by number rather than by alpha character. This is done, because ba33, for example, is a part that is detailed on sheet 33. I need my list sorted with parts on the same sheet grouped together before proceeding to parts on the next sheet.

If I were able to insert the required function into cell D3, I would need only to highlight cells B3 to E3 and autofill down to accomodate whatever data is in column A.

If you see a way to define a function in this way, and could post it, it would be greatly appreciated. Otherwise, the posts you have already made will be a great help to me.

Thank you very much !!!
 
Upvote 0
If you mean a user defined function then I think your out of luck.

The way I'm reading your posts is that you would want to put that function in one cell and have it change something in another cell.

That just won't work.:)
 
Upvote 0
Actually, the user defined function would go in cell D3 and would replace itself with the value derived from the formula in cell C3.

I'm beginning to think that what I've requested isn't possible, though. I appreciate all of the replies, and I apologize for my lack of comprehension here.

Thanks !!
 
Upvote 0
it sounds like you want a formula to get rid of formula but your using a formula so it doesn't work - hope that made sense.

there are many things you can do with code though. you can have it calculate values and then paste special every time a certain range changes etc.

many options
 
Upvote 0
Exactly !! Made perfect sense !!

Thanks !! That cleared everything up for me. I'm sorry I was so dense ! Without that explanation, however, I just couldn't see why things wouldn't work the way I had hoped. I thought I just wasn't making clear what I wanted to do.

Either way, I had given up on making it work. I revised my approach to the whole worksheet and created a macro and a "Go" button that does everything after the initial paste into column A from the clipboard.

That only succeeded because of previous posts on this and another forum where I was soliciting help from, though.

Thanks to all who responded !!!!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top