Read a range into a Variant array, loop in memory, and write the whole array back once. Each cell access is a slow call into Excel; an array loop is pure VBA and hundreds of times faster.

Code

v = rng.Value
For i = 1 To UBound(v, 1): v(i, 2) = v(i, 1) * 2: Next
rng.Value = v

• Read, loop, write

  v = rng.Value For i = 1 To UBound(v, 1): v(i, 2) = v(i, 1) * 2: Next rng.Value = v → Column 2 becomes double column 1, in one read and one write

  The pattern.

• Scan a column

  v = Range("A2:A10000").Value For i = 1 To UBound(v, 1) If v(i, 1) < 0 Then n = n + 1 Next → Counts negatives in 10,000 rows instantly

  Read-only scans.

• Output array

  ReDim out(1 To UBound(v, 1), 1 To 1) ... Range("B2").Resize(UBound(out, 1), 1).Value = out → A separate output array written to a resized target

  Build results separately.

• Arrays from ranges are always 2D and 1-based.
• Resize the target to match before writing.
• Use Value2 to skip date and currency conversion.

• Writing a 1D array to a column writes only the first element unless transposed.
• Errors in cells come through as Error variants; test with IsError.