Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 Recognize Formula
I've modified an existing macro that copies one range to another by pasting
values. The source range has formulas that use CONCATENATE to build a formula by looking up values in several cells. That part works ok, but when I paste it as values, I need to edit each cell and change the = to = (in other words, fool Excel into thinking I've just entered this brilliant formula on my own. Otherwise, Excel just shows the pasted formula instead of the results). The code below did it on a range that is some distance away from the original range. This time, I just want to do it to a specific range called 'AZInputs[TestPaste]'. What change would I need to make to the code below in order to make this work? With myRngToCopy .Copy With .Offset(0, -12) .PasteSpecial Paste:=xlPasteValues 'Selection.Style = "Calculation" With .Resize(, 2) 'same number of rows, but two columns .Replace What:="=", _ Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End With End With End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 Recognize Formula
Ok. You code does this; Copies a defined range; With myRngToCopy MyRngToCopy is tha name of a range. If you wanted to copy another range you could change this line to; Dim thisrange thisrange = activesheet.range("a1:z10") with this range Then it copies the range * * * * .Copy Goes to where it wants to paste it (offset 0 rows and -12 columns from the activecell) * * * * With .Offset(0, -12) Pastes it * * * * * * .PasteSpecial Paste:=xlPasteValues Then runs the code to change the values; * * * * * * 'Selection.Style = "Calculation" * * * * * * With .Resize(, 2) 'same number of rows, but two columns * * * * * * * *.Replace What:="=", _ * * * * * * * * * *Replacement:="=", _ * * * * * * * * * *LookAt:=xlPart, _ * * * * * * * * * *SearchOrder:=xlByRows, _ * * * * * * * * * *MatchCase:=False, _ * * * * * * * * * *SearchFormat:=False, _ * * * * * * * * * *ReplaceFormat:=False * * * * * *End With * * * * End With * * End With If you have already copied and pasted the range you want to change and simply want to run the value changing part of the code on this range you simply need; with activesheet.range(AZInputs[TestPaste]) 'Selection.Style = "Calculation" With .Resize(, 2) 'same number of rows, but two columns .Replace What:="=", _ Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End With End With End With However I am bemused as to why you would need to change the =. I have tried copying/pasting some test formula and have no problem with it showing immediately as a value and not as a formula where i would need to replace the = to make it work. (Have you tried copying/pasting manually to see the results?) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 Recognize Formula
Anon,
Well, I've been exerimenting with this lookup function and pulling it's references from neighboring cells to build my formula. Previously, it never worked. It would turn the internal indirect part of it into text instead of the reference I needed it to be. Today it's working. I'm not sure what I was doing wrong before, but I'm glad it works. Now I don't even need a macro. Thanks for your help with the bemusement statement. Mark. "anon" wrote: Ok. You code does this; Copies a defined range; With myRngToCopy MyRngToCopy is tha name of a range. If you wanted to copy another range you could change this line to; Dim thisrange thisrange = activesheet.range("a1:z10") with this range Then it copies the range .Copy Goes to where it wants to paste it (offset 0 rows and -12 columns from the activecell) With .Offset(0, -12) Pastes it .PasteSpecial Paste:=xlPasteValues Then runs the code to change the values; 'Selection.Style = "Calculation" With .Resize(, 2) 'same number of rows, but two columns .Replace What:="=", _ Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End With End With End With If you have already copied and pasted the range you want to change and simply want to run the value changing part of the code on this range you simply need; with activesheet.range(AZInputs[TestPaste]) 'Selection.Style = "Calculation" With .Resize(, 2) 'same number of rows, but two columns .Replace What:="=", _ Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False End With End With End With However I am bemused as to why you would need to change the =. I have tried copying/pasting some test formula and have no problem with it showing immediately as a value and not as a formula where i would need to replace the = to make it work. (Have you tried copying/pasting manually to see the results?) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
outlook 2007 wont recognize my profiles | Excel Discussion (Misc queries) | |||
Excel does not recognize the word Desktop in a countif formula? | Excel Worksheet Functions | |||
recognize formula | Excel Worksheet Functions | |||
Can excel recognize a word and auto format a formula, on diffe she | Excel Worksheet Functions | |||
excel formula to recognize overtime in a day as you prep schedule | Excel Programming |