Converting an entire sheet to Metric and back

[pgag45]

Hi all,

I would like to convert an entire Excel sheet, or at least most of it, to metric units (multiplying by 1.12 for this particular unit). The entire range I wish to convert can be anywhere from 8 cols x 30 rows to 8 cols x 10k rows. What would be the most efficient way to solve this problem? Just a huge loop over the range of the cells I wish to convert, multiplying (or dividing if going back to English) each cell value by 1.12? Or is there a more efficient approach?

Thanks Much

[VBAhack]

Looping through individual cells in a range can be slow. It is better to read the range directly into an array, modify the array, and write it back out:

Sub RangeMod()
    Dim nrow As Integer, ncol As Integer, i As Integer, j As Integer
    Dim mf As Double
    Dim a As Variant
    
    mf = 1.15
    nrow = 27
    ncol = 8
    
    a = Range("D5").Resize(nrow, ncol)      'read range into array
    
    For i = 1 To nrow                       'loop through array
        For j = 1 To ncol                   'loop through array
            a(i, j) = mf * a(i, j)          'modify array elements
        Next j
    Next i
    
    Range("D5").Resize(nrow, ncol) = a      'write array back to range
End Sub