Apply a function to the entire column

[Amjad]

Hi,
I have an Excel column full of data imported from a text
file. How do I apply an Excel function on each cell in
that column and print the result in the next column using
VBA? I don't want the user to see the function that I'm
using.

I know that I could type the function in the formula bar
and apply AutoFill across the column, but this will make
my function visible to the user.

I'm trying to avoid scanning the whole column (cell by
cell) using a FOR loop because it takes a long time.

I feel there is an easier way to do it.. please let me
know if you have an idea.

Amjad

 

[Jamie Martin]

I am new to this, so investigate my advice before you follow it. However, I
know that you can turn off screen updating while your procedure is running
and then turn it back on once it's done; I think this would solve your
problem, I'm just not sure if there are any dangers to turning off screen
updating.

 

[Tom Ogilvy]

Assume data is in column D and you want the results in column E

Dim rng as Range
Application.ScreenUpdating = False
set rng = Range(Cells(1,"D"),Cells(1,"D").End(xldown))
rng.offset(0,1).Formula = "=Round(" & rng(1).Address(0,0) & "/100,2)"
rng.offset(0,1).Formula = rng.offset(0,1).Value
Application.ScreenUpdating = True

the formula is just a sample of how you would enter your formula.

the last line replaces the formula with the values it produces.