On 2002-03-25 08:45, verluc wrote:
How can I whrite a macro that delete all the formules in column B (+/- 7000 lines)without deleted the values in the column?
Many thanks.
Matt, you need to change your first line of code to read:On 2002-03-25 08:59, Matt wrote:
The following code will overwrite the formula with the values
Range("A1:A7000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
HTH
Matt
On 2002-03-25 17:12, Yogi Anand wrote:
Hi Jack:
Your assumption is that VERLUC's post means that in some cells in column B there are formulas, and in some other cells there are static values -- in that case what you are suggesting will work wonderfully.
Barrie and Matt have assumed that VERLUC's post implies that all the cells in column B are formulas and now there is need to convert those into static values and get rid of the formulas -- I believe this is what VERLUC's post implies.
VERUC: please post back to confirm what you really want o accomplish.
On 2002-03-25 18:23, Anonymuus wrote:
On 2002-03-25 17:12, Yogi Anand wrote:
Hi Jack:
Your assumption is that VERLUC's post means that in some cells in column B there are formulas, and in some other cells there are static values -- in that case what you are suggesting will work wonderfully.
Barrie and Matt have assumed that VERLUC's post implies that all the cells in column B are formulas and now there is need to convert those into static values and get rid of the formulas -- I believe this is what VERLUC's post implies.
VERUC: please post back to confirm what you really want o accomplish.
Your summary of the suggested solutions is not correct.
Jack's solution clears the contents of the cells containing formulas, leaving blank cells.
Barrie/Matt's solution converts cells with formulas to values only. (Also, it does not assume all cells contain formulas.)
The VBA code to do it could be reduced to :-
Columns(2) = Columns(2).Value
On 2002-03-25 09:10, Barrie Davidson wrote:
Matt, you need to change your first line of code to read:On 2002-03-25 08:59, Matt wrote:
The following code will overwrite the formula with the values
Range("A1:A7000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
HTH
Matt
Range("B1:B7000").Select
Going with Matt's code, you could try:
<pre>
Range("B1:" & Range("B65536").End(xlUp).Address).Copy
Range("B1:" & Range("B65536").End(xlUp).Address).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
</pre>
Regards,
I have create the macro above and it works
correct.But that was not the total question.
What's my problem?
I have on sheet1 all the symbols of the nasdaq stock exhange.Colum A1 to A7000
In colum B1 to B7000 I set the stock prices who are downloaded in sheet2 with a formule that I have for each line.
The following day I downloaded the new stock prices in sheet2(overwriting) and I want these new stock prices in colum C1 tot C7000
So I needed my formules from colum B in C and deleted the formules in B and so on each day so I can follow the stock prices each day.
Have you therefore a macro to do that automaticly?
Many thanks.