Following macro replaces negative values in the range A2:I30 with 1.
Sub replace() Dim rng As Range Application.ScreenUpdating = False With Workbooks("Book1.xlsm").Sheets("Sheet2") For Each rng In Range("A2:I30") If rng.Value < 0 Then rng = 1 End If Next rng End With Application.ScreenUpdating = True End Sub
The macro runs fine when the current active worksheet is same as worksheet Sheet2. However, if the current active worksheet OR workbook is different from Book1.xlsm or Sheet2, the macro either throws error (if run in background through another macro OR simply does not run if run by a button on another Worksheet).
Can someone please point out what am I missing here.
Note: Sample file attached.