Using rng.Value & RefersToRange to pass data between worksheets - SLOW!
I am writing a VBA application that identifies a named cell in Workbook1 and
then passes a value from an Array in Workbook2 to the named cells in
Workbook1. There are from 1 to 10 items in the Array. When all the items
have been passed, Workbook1 is recalculated and a named range is passed back
to Workbook2. The code works fine, but it is VERY SLOW. Can someone
suggest an alternative method for passing the Array data into the named
cells?
The code I am using looks like this:
Public uNameArray()
ReDim uNameArray(1 to 1, 1 to NumberOfArrayItems)
For y = 1 to NumberOfArrayItems
'Get the cell name from the Array
uName = uNameArray(1, y)
'Set the value of the named cell in Workbook 1 equal to SomeVAlue
Set rng.Value = Workbook1.Names(uName).RefersToRange
rng.Value = SomeValue
Next y
I would appreciate any help from the group.
Frank
|