Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.PasteSpecial (xlPasteValues) hangs program
I have a worksheet with formula that auto-eneters the date (NOW()),
based on the value of another cell. The cell is either todays date, or an emopty string. ==IF(I26=0,NOW(),"") When exiting the sheet I want to convert any dates entered to thier literal value (i.e remove the formula) so that the date is not overwritten with a future date when the sheet is reopened. This code works, but takes a very long time , and when stepping through I see it hangs for several seconds on each call to pastespecial, making it take over 10 minutes for 3000 rows. Any reason for this - is thier a faster way to achieve the same result? thanks,hals_left For Each Cell In Worksheets(1).Range("myRange") If IsDate(Cell.Value) Then Cell.Select Cell.Copy ActiveCell.PasteSpecial (xlPasteValues) End If Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.PasteSpecial (xlPasteValues) hangs program
Try the following
Sub ReplaceIt() Dim a As Range For Each a In Worksheets(1).Range("myRange").Areas a.Copy a.PasteSpecial xlPasteValues Next a End Sub Kevin Beckham -----Original Message----- I have a worksheet with formula that auto-eneters the date (NOW()), based on the value of another cell. The cell is either todays date, or an emopty string. ==IF(I26=0,NOW(),"") When exiting the sheet I want to convert any dates entered to thier literal value (i.e remove the formula) so that the date is not overwritten with a future date when the sheet is reopened. This code works, but takes a very long time , and when stepping through I see it hangs for several seconds on each call to pastespecial, making it take over 10 minutes for 3000 rows. Any reason for this - is thier a faster way to achieve the same result? thanks,hals_left For Each Cell In Worksheets(1).Range("myRange") If IsDate(Cell.Value) Then Cell.Select Cell.Copy ActiveCell.PasteSpecial (xlPasteValues) End If Next . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.PasteSpecial (xlPasteValues) hangs program
Following is an alternative to kevin's code a.formula=a.value keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Kevin Beckham" wrote: Try the following Sub ReplaceIt() Dim a As Range For Each a In Worksheets(1).Range("myRange").Areas a.Copy a.PasteSpecial xlPasteValues Next a End Sub Kevin Beckham -----Original Message----- I have a worksheet with formula that auto-eneters the date (NOW()), based on the value of another cell. The cell is either todays date, or an emopty string. ==IF(I26=0,NOW(),"") When exiting the sheet I want to convert any dates entered to thier literal value (i.e remove the formula) so that the date is not overwritten with a future date when the sheet is reopened. This code works, but takes a very long time , and when stepping through I see it hangs for several seconds on each call to pastespecial, making it take over 10 minutes for 3000 rows. Any reason for this - is thier a faster way to achieve the same result? thanks,hals_left For Each Cell In Worksheets(1).Range("myRange") If IsDate(Cell.Value) Then Cell.Select Cell.Copy ActiveCell.PasteSpecial (xlPasteValues) End If Next . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.PasteSpecial (xlPasteValues) hangs program
The areas doesnt seem to work at all, formulae are left in the cells
with a valid date, so wjhen i re-open the worksheet another day, the dates change. Sub toText() Dim objRange As Range For Each objRange In Worksheets(1).Range("dateColumn").Areas If IsDate(objRange.Value) Then objRange.Formula = objRange.Value End If Next End Sub "Kevin Beckham" wrote in message ... Try the following Sub ReplaceIt() Dim a As Range For Each a In Worksheets(1).Range("myRange").Areas a.Copy a.PasteSpecial xlPasteValues Next a End Sub Kevin Beckham -----Original Message----- I have a worksheet with formula that auto-eneters the date (NOW()), based on the value of another cell. The cell is either todays date, or an emopty string. ==IF(I26=0,NOW(),"") When exiting the sheet I want to convert any dates entered to thier literal value (i.e remove the formula) so that the date is not overwritten with a future date when the sheet is reopened. This code works, but takes a very long time , and when stepping through I see it hangs for several seconds on each call to pastespecial, making it take over 10 minutes for 3000 rows. Any reason for this - is thier a faster way to achieve the same result? thanks,hals_left For Each Cell In Worksheets(1).Range("myRange") If IsDate(Cell.Value) Then Cell.Select Cell.Copy ActiveCell.PasteSpecial (xlPasteValues) End If Next . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
2007 - Program Hangs when Viewing Worksheets | Excel Discussion (Misc queries) | |||
pastespecial | Excel Programming | |||
pastespecial in vba | Excel Programming | |||
merging excel program with tdc finance program | Excel Programming |