On Jan 24, 9:25*pm, John wrote:
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!

Why not just have spreadsheet formulas in the second range of cells?
VBA seems like overkill. But - assuming that you have good reasons for
wanting a VBA approach, I suspect that one of the problems is that you
are constanly computing named ranges. Perhaps you can do something
like

Sub test()
Dim R As Range, S As Range
Dim i As Long, n As Long

Set R = Range("A1:A3")
Set S = Range("B1:B3")

n = R.Cells.Count
For i = 1 To n
If R.Cells(i).Value Then S.Cells(i).Value = "OK"
Next i

End Sub

For larger ranges you can read the values of the first range into a
variant array, loop through that array while building up a second
array and tranfer the second array to the second range. This is
somewhat annoying in the case of 1-column ranges (since you somewhat
oddly need to use the worksheet transpose function) but would be much
easier if the two ranges are in adjacent columns (since then you could
get by with a single 2-dimensional array which encompasses both
ranges).