When I loop through in VBA setting a value for a variable the loop goes
fast. When I use cell(i,j).value to put the value in a cell there is about a
1 second delay between each cell. For 250 cells it is a long wait. Is there
any way to control this wait?

add
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

to the beginning of your code

and

Application.ScreenUpdating = true
Application.Calculation = xlCalculationAutomatic

at the end

--


Gary

"Dick HSV" <Dick (E-Mail Removed)> wrote in message
news:33D52851-B982-4C85-A507-(E-Mail Removed)...
> When I loop through in VBA setting a value for a variable the loop goes
> fast. When I use cell(i,j).value to put the value in a cell there is about a
> 1 second delay between each cell. For 250 cells it is a long wait. Is there
> any way to control this wait?

Gary,

Thanks alot. New to Excel macros - was stumped.

Dick HSV

"Gary Keramidas" wrote:

> add
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>
> to the beginning of your code
>
> and
>
> Application.ScreenUpdating = true
> Application.Calculation = xlCalculationAutomatic
>
> at the end
>
> --
>
>
> Gary
>
> "Dick HSV" <Dick (E-Mail Removed)> wrote in message
> news:33D52851-B982-4C85-A507-(E-Mail Removed)...
> > When I loop through in VBA setting a value for a variable the loop goes
> > fast. When I use cell(i,j).value to put the value in a cell there is about a
> > 1 second delay between each cell. For 250 cells it is a long wait. Is there
> > any way to control this wait?
>
>
>

Also, read the range directly into a VB array, and if necessary, loop
through the array in memory. Then write the entire array to a range in one
step.

Sample Usage:

Reading:
Dim vArray As Variant ' must be "variant" to read range into VBA array
vArray = ActiveSheet.Range("A110").Value

Writing:
' can be array of any variable type
ActiveSheet.Range("A110").Value = myArray

This will produce a great improvement in execution speed, particularly
writing back to the sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Gary Keramidas" <GKeramidasAtMsn.com> wrote in message
news:(E-Mail Removed)...
> add
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>
> to the beginning of your code
>
> and
>
> Application.ScreenUpdating = true
> Application.Calculation = xlCalculationAutomatic
>
> at the end
>
> --
>
>
> Gary
>
> "Dick HSV" <Dick (E-Mail Removed)> wrote in message
> news:33D52851-B982-4C85-A507-(E-Mail Removed)...
>> When I loop through in VBA setting a value for a variable the loop goes
>> fast. When I use cell(i,j).value to put the value in a cell there is
>> about a
>> 1 second delay between each cell. For 250 cells it is a long wait. Is
>> there
>> any way to control this wait?
>
>