Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro to re-write all cells in a column
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Macro to re-write all cells in a column
rewrite to where? more info please...


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Macro to re-write all cells in a column
rewrite in the same cell.

I tried the following

Sub RewriteColumn()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set isect = Intersect(Columns(ActiveCell.Column), ActiveSheet.UsedRange)
If isect Is Nothing Then
MsgBox "This is an empty column!"
Exit Sub
End If

For Each cel In isect
valu = cel.Value
cel.FormulaR1C1 = valu
Next cel

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


"sp00nix " wrote:

rewrite to where? more info please...


---
Message posted from http://www.ExcelForum.com/


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Macro to re-write all cells in a column
That worked fine for me. What is the problem?

--
Regards,
Tom Ogilvy

"Bob L" wrote in message
...
rewrite in the same cell.

I tried the following

Sub RewriteColumn()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set isect = Intersect(Columns(ActiveCell.Column),
ActiveSheet.UsedRange)
If isect Is Nothing Then
MsgBox "This is an empty column!"
Exit Sub
End If

For Each cel In isect
valu = cel.Value
cel.FormulaR1C1 = valu
Next cel

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


"sp00nix " wrote:

rewrite to where? more info please...


---
Message posted from http://www.ExcelForum.com/




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Macro to re-write all cells in a column
It doesn't seem to rewrite, I have over 9500 cells and it doesn't seem to work


"Tom Ogilvy" wrote:

That worked fine for me. What is the problem?

--
Regards,
Tom Ogilvy

"Bob L" wrote in message
...
rewrite in the same cell.

I tried the following

Sub RewriteColumn()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set isect = Intersect(Columns(ActiveCell.Column),
ActiveSheet.UsedRange)
If isect Is Nothing Then
MsgBox "This is an empty column!"
Exit Sub
End If

For Each cel In isect
valu = cel.Value
cel.FormulaR1C1 = valu
Next cel

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


"sp00nix " wrote:

rewrite to where? more info please...


---
Message posted from http://www.ExcelForum.com/





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Macro to re-write all cells in a column
Run this version

Sub RewriteColumn()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set isect = Intersect(Columns(ActiveCell.Column), ActiveSheet.UsedRange)
If isect Is Nothing Then
MsgBox "This is an empty column!"
Exit Sub
End If

For Each cel In isect
if cel.row mod 500 = 0 then _
msgbox "processing Cell " & cell.Address
valu = cel.Value
cel.FormulaR1C1 = valu
Next cel

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

--
Regards,
Tom Ogilvy



"Bob L" wrote in message
...
It doesn't seem to rewrite, I have over 9500 cells and it doesn't seem to
work


"Tom Ogilvy" wrote:

That worked fine for me. What is the problem?

--
Regards,
Tom Ogilvy

"Bob L" wrote in message
...
rewrite in the same cell.

I tried the following

Sub RewriteColumn()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set isect = Intersect(Columns(ActiveCell.Column),
ActiveSheet.UsedRange)
If isect Is Nothing Then
MsgBox "This is an empty column!"
Exit Sub
End If

For Each cel In isect
valu = cel.Value
cel.FormulaR1C1 = valu
Next cel

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


"sp00nix " wrote:

rewrite to where? more info please...


---
Message posted from http://www.ExcelForum.com/







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to write a macro that will clear validated cells to blank? JAbels001 Excel Worksheet Functions 3 November 20th 08 05:20 PM
write a macro for variable cells Garry Excel Discussion (Misc queries) 0 March 22nd 06 07:45 PM
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? Daniel Excel Worksheet Functions 1 June 23rd 05 11:38 PM
Write a macro to populate a column with a formula martinjw[_2_] Excel Programming 2 May 26th 04 05:48 PM
Write a macro ro copy column at certain time of day. Daytrader Excel Programming 2 February 2nd 04 05:28 AM


All times are GMT +1. The time now is 10:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement