For, Next - How to Speed Up
This is butter. Thanks, Isabelle.
On Jan 24, 8:52*pm, isabelle wrote:
hi,
With Range("vbChkRefStatus")
* * *.Value = wsMenu.Range("rngChkRef").Value
* * *.Replace What:=True, Replacement:="OK", LookAt:=xlPart
* * *.Replace What:=False, Replacement:="", LookAt:=xlPart
End With
--
isabelle
Le 2012-01-24 21:25, John a écrit :
Looking for tips to speed up a loop that writes a value to a range of
cells given the value of related cells.
Details:
I have two ranges---both contain one column and same number of rows
(10). Each of the 3 cells is either TRUE or FALSE. The second range
has 3 cells. VBA writes a value to the second range for each cell
where TRUE in the first named range.
Code:
* * * * *Dim i As Integer
* * * * * * * * * * *For i = 1 To 3
* * * * * * * * * * * * *If wsMenu.Range("rngChkRef"& *i) Then
* * * * * * * * * * * * * * *wsMenu.Range("vbChkRefStatus"& *i) = "OK"
* * * * * * * * * * * * *End if
* * * * * * * * * * *Next i
Given:
A1 TRUE
A2 FALSE
A3 TRUE
Loop result:
B1 (vba writes OK)
B2
B3 (vba writes OK)
Is there any way to speed this up? Tried ScreenUpdating and
calculation but the loop writes are noticeable (ie, template contains
more than 3 cells). I'm thinking an array would be fast but not sure
how to write it. Any help is appreciated!- Hide quoted text -
- Show quoted text -
|