VBA select range to multiply with number but need to setup loop

Question

So I have the sub to select a range and to multiply the range with a certain number based on Input Boxes. But I can't get it to loop through all the cells in the range.

I have the following sub which I have made out of separate other subs that I use:

Sub somesub()
Dim xVRg As Range
Set xVRg = Application.InputBox("Please select range you want to multiply:", "", Type:=8)
Dim Mnumber As Double
Mnumber = Application.InputBox("Enter number", "Multiply", Type:=1)

For Each xVRg In Selection
    If IsNumeric(xVRg) Then
    xVRg.Value = xVRg.Value * Mnumber
    End If
Next

End Sub

So this sub lets me select the range and let me input the number by which I want to have it multiplied. I only can't get it looped through, so now only the cell that is selected will be changed and not the range that I have selected.

Do you have a tip to loop through the multiplier to ensure it loops through all the cells inside the range?

Answer 1

Please, try the next adapted code. You select it only to Set it, it does not remain selected:

Sub somesub()
Dim xVRg As Range, cel As Range
Set xVRg = Application.InputBox("Please select range you want to multiply:", "", Type:=8)
Dim Mnumber As Double
Mnumber = Application.InputBox("Enter number", "Multiply", Type:=1)

For Each cel In xVRg
    If IsNumeric(cel) Then
    cel.Value = cel.Value * Mnumber
    End If
Next

End Sub

A faster version, using an array and processing only in memory, dropping the result a once, at the code end, will be the next one:

Sub somesubArray()
  Dim xVRg As Range, cel As Range, arr, i As Long, j As Long
 Set xVRg = Application.InputBox("Please select range you want to multiply:", "", Type:=8)
 Dim Mnumber As Double
 Mnumber = Application.InputBox("Enter number", "Multiply", Type:=1)

 arr = xVRg.Value2
 For i = 1 To UBound(arr)
    For j = 1 To UBound(arr, 2)
        If IsNumeric(arr(i, j)) Then
            arr(i, j) = arr(i, j) * Mnumber
        End If
    Next j
 Next i

 'drop back the processed array:
 xVRg.Value2 = arr
End Sub