Seems like a fun problem. my attempt:
Code
Sub AddSubDivMulRange(v As Double, r As Range, op As String, Optional skipBlanks As Boolean = True)
Dim c As Range, psOp As XlPasteSpecialOperation
Select Case op
Case "+": psOp = xlPasteSpecialOperationAdd
Case "-": psOp = xlPasteSpecialOperationSubtract
Case "*": psOp = xlPasteSpecialOperationMultiply
Case "/": psOp = xlPasteSpecialOperationDivide
End Select
If psOp <> 0 Then
Set c = r.Parent.UsedRange.SpecialCells(xlCellTypeLastCell)(2, 1)
Application.ScreenUpdating = False
c.Value = v
c.Copy
If skipBlanks Then
On Error Resume Next
r.SpecialCells(xlCellTypeConstants).PasteSpecial xlPasteValues, psOp
r.SpecialCells(xlCellTypeFormulas).PasteSpecial xlPasteValues, psOp
On Error GoTo 0
Else
r.PasteSpecial xlPasteValues, psOp
End If
Application.CutCopyMode = False
c.Clear
Application.ScreenUpdating = True
Set c = Nothing
End If
End Sub
Display More