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

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
.

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
.

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
.