Enter formula in a range across all sheets

[nickisokay]

Hello,
I have a userform where someone can enter a multiplying factor (exchange rate).
I want to use this number to multiply the contents of a range of cells (A1:A100) across all visible sheets.
Can you help please!
Thanks,
Nick

 

[steve the fish]

Are these cells formulas or static values?

 

[nickisokay]

Are these cells formulas or static values?

Static values. Would be good if the end result leaves them as static values too.

Thanks,
Nick

 

[steve the fish]

Something like this will work. The multiplier will be a textbox text on the userform.

Dim sh As Worksheet, arr, multiplier As Double

multiplier = 45

For Each sh In ThisWorkbook.Worksheets
    If sh.Visible = True Then
        arr = sh.Range("A1:A100")
        For i = 1 To 100
            If Not IsEmpty(arr(i, 1)) And IsNumeric(arr(i, 1)) Then
                arr(i, 1) = arr(i, 1) * multiplier
            End If
        Next
        sh.Range("A1:A100") = arr
    End If
Next

 

[nickisokay]

Forgot to say thank you Steve - this worked a treat and has saved me some time!! Many thanks