Convert Formula Result to Static Text

[MYGS]

Hi Guys,

I've searched high and low but can't seem to locate a solution, so here I am.

How can you dynamically convert a column containing formula results to static text?

Obviously it's easy to select the column and manually past values only, but how would you set this up for Excel to do it manually, ie. copy results to a new column ready for editing?

Thanks...

[Catalin.B]

try a simple macro...
Sub Macrocomandă1()
Range("H10:H25").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

[MYGS]

Thanks Catalin I will check out and let you know how it goes....

[brainstewn]

Originally Posted by Catalin.B

try a simple macro...

Hi Catalin,

Can your macro be formatted to copy a date to static text? If I use your formula I get the "40910" results in the copy to column.

[Catalin.B]

Format that cell as Date format, the number you see as the result is a date in numeric format (it's the number of days from 01.01.1900)

[OTPM]

Hi
You could attach Catalin's code to the Worksheet rather than a module using the Worksheet_Change event. That way as soon as you change a cell the code will run automatically each time a change is made.
Hope this helps.
Tony

[MYGS]

HI Catalin,

Yes that macro works.

So how now do I set it up to run dynamically so that the contents of column 'H' (the formula results) are automatically copied into column 'I' (the static text for editing)? I can't seem to figure that out.

Thank you.

[Catalin.B]

Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause execution of macrocomandă1 when they are changed. Set KeyCells = Range("H10:H1000") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Macrocomandă1 End If End Sub

This macro will execute the first macro whenever a cell in range H10:H1000 is modified..

[MYGS]

Hi Catalina,

Thanks for your post. However I can't get it work. Can you please take a look at my code and let me know what I did wrong? Thanks.

=================================================
Sub CopyFormulaResults()
Range("H10:H25").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

=================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("H10:H25")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
CopyFormulaResults
End If
End Sub

[Catalin.B]

enter this macros in a module :

Sub auto_open()
' works when you double click a cell and press enter, not when formula result changes, or when you copy formula to range
' whenever you need to update , double click on any cell then press enter
ThisWorkbook.Worksheets("Foaie2").OnEntry = "CopyFormulaResults"

End Sub

Sub CopyFormulaResults()
Range("H10:H50").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

[MYGS]

Hi Catalin,

Wow you're a genius!!! LOL Works perfect... oh and you're a life saver too as I really wasn't sure how I was going to get around this issue.

The only thing now is how can I execute this on the entire workbook?

I tried replacing: ThisWorkbook.Worksheets("TEST") =

with: ActiveWorkbook.OnEntry =

it works but I received an error when I re-opened the file again. Any ideas?

Thanks again... you rock!

[Catalin.B]

replace sub auto open with this:

Sub auto_open()
Dim WS_Count As Integer
Dim i As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
ThisWorkbook.Worksheets(i).OnEntry = "CopyFormulaResults"
Next
End Sub

[MYGS]

Hi Catalin,

Thanks that works.

Now I've got this working is there any way to make the process more automated?
As some people will probably complain if they have to double-click and enter after every change. Can we do a refresh on any cell change?

Thanks.

[Catalin.B]

how much more automated? when they blink ?
you mean you never open a cell and enter data on this workbook?
Workbook_Change works only with data, does not work if your range contains formulas..this is a far more complicated macro

maybe an expert will give you a better solution

[MYGS]

Yes 'on blinking' would be good... LOL

The column doesn't get updated when you change a cell, only when you do the double-click + ENTER process, I don't know anyone who does this, do you? Of course to do the dbl-click+enter every time you make a change just to update the results column would pretty much defeat the purpose of making it automated as its easier to click the macro button.

I though I read somewhere that there is a function to execute a macro on any cell change (Workbook wide) that would work because as soon as you enter the new information the column would update.

Any ideas? Tks...