Macro to re-write all cells in a column

Macro to re-write all cells in a column

Post by sp00ni » Thu, 12 Aug 2004 06:43:14


rewrite to where? more info please...


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

Macro to re-write all cells in a column

Post by Gord Dibbe » Thu, 12 Aug 2004 06:54:13

Bob

It should be possible.

"re-write all the contents" means what?

What do you have now and what do you want to see after "re-writing"?

Do you want to add to the existing data or put new and unique data in the
cells?

Gord Dibben Excel MVP

On Tue, 10 Aug 2004 13:45:03 -0700, "Bob L" < XXXX@XXXXX.COM >

 
 
 

Macro to re-write all cells in a column

Post by Tom Ogilv » Thu, 12 Aug 2004 06:57:47

columns(3).copy
columns(3).Pastespecial xlValues

or

set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup))
for each cell in rng
cell.formula = cell.Value
Next

adjust for formulas.

--
Regards,
Tom Ogilvy
 
 
 

Macro to re-write all cells in a column

Post by Qm9iIE » Thu, 12 Aug 2004 07:03:02

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
 
 
 

Macro to re-write all cells in a column

Post by Tom Ogilv » Thu, 12 Aug 2004 07:08:11

That worked fine for me. What is the problem?

--
Regards,
Tom Ogilvy



ActiveSheet.UsedRange)
 
 
 

Macro to re-write all cells in a column

Post by Qm9iIE » Thu, 12 Aug 2004 07:17:01

Gord,

I have both text and numbers, I want it to just rewrite itself.
I am using a vlookup formula that won't work unless I rewrite the cells in
the lookup column and I can't figure out any other way but to rewrite the
contents.

Bob
 
 
 

Macro to re-write all cells in a column

Post by Qm9iIE » Thu, 12 Aug 2004 07:47:02

It doesn't seem to rewrite, I have over 9500 cells and it doesn't seem to work
 
 
 

Macro to re-write all cells in a column

Post by Tom Ogilv » Thu, 12 Aug 2004 08:06:09

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





work
 
 
 

Macro to re-write all cells in a column

Post by Tom Ogilv » Thu, 12 Aug 2004 08:08:08

if you have numbers being treated as text, select the entirecolumn and
format as General, then do Data=>Text to columns, select delimited, and
click OK. (if you have commas in your data, select a delimiter like # that
doesn't exist in the data).

This should cause you data to be reevaluated.

--
Regards,
Tom Ogilvy




the
< XXXX@XXXXX.COM >

the
 
 
 

Macro to re-write all cells in a column

Post by Qm9iIE » Fri, 13 Aug 2004 03:43:02

Tom,

That reevaluated the text but it did not recalculate, which is what I think
it needs to do. I have checked my settings under the calculation tab and all
is o.k. I don't know what is causing this not to work but I am completley
frustrated. I can go to the cell and retype the contents manually then go to
my sheet that has the VLOOKUP formaula and the cell is filled in properly.
Any other ideas?

Bob