#1
|
|||
|
|||
Convert Formula Result to Static Text
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... |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Thanks Catalin I will check out and let you know how it goes....
|
#4
|
|||
|
|||
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. Last edited by MYGS; 08-15-2011 at 04:38 AM. Reason: clarification |
#5
|
|||
|
|||
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.. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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! |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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... |
#13
|
|||
|
|||
you can setup cell A1 to decide if the macro should run.
info about events :http://www.cpearson.com/excel/events.htm but will work only when you change the value in cell A1,it will not fire when a formula result changes, so this means it will work the same way How do I run a macro every time a certain cell changes its value? There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in:Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Else 'Do whatever you need to do... End If End Sub |
#14
|
|||
|
|||
Quote:
Most of the people i know, between double click and enter, is typing data, or writing formulas... Another idea for you is to run your macro every 30 seconds, or less ... Sub CopyFormulaResults() Application.OnTime Now + TimeValue("00:00:10"), "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 |
#15
|
|||
|
|||
Convert Formula resulting in a DATE to static text
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Merge Fields and Static Text | kbranden | Mail Merge | 1 | 06-15-2011 09:02 AM |
Making items (text, pictures, etc.) static on page | Jeremiahts | Word | 3 | 04-07-2011 09:54 PM |
convert html to text at opening | etfjr | Word | 0 | 12-13-2010 11:14 AM |
Convert Number to Text | devcon | Word | 0 | 07-10-2010 01:16 AM |
Checkbox on Userform result in Text in Word | Dolfie_twee | Word VBA | 1 | 06-22-2010 07:54 AM |