How to multiply whole row in a range by number

Question

I am writing a function that adds or multiplies all values in a chosen row in an array by number (x). But in the part

If method = 1 Then
 rng.Rows(row_number) = rng.Rows(row_number) * x
  ElseIf method = 2 Then
  rng.Rows(row_number) = rng.Rows(row_number) + x
  End If

I get the error message "Run time error 424; object required".

Besides this code, I tried to use .Select or .entirerow.select, but none of them worked.

Function shiftMatrix(rg As Range, row_number As Long, x As Double, method As Integer) As Variant
    Dim rng As Variant
rng = rg.value
If method = 1 Then
 rng.Rows(row_number) = rng.Rows(row_number) * x
  ElseIf method = 2 Then
  rng.Rows(row_number) = rng.Rows(row_number) + x
  End If
  shiftMatrix = rng
  End Function
  Sub try() '     
Dim rng As Variant
 With Worksheets("Macro1")
        rng = shiftMatrix(Worksheets("Macro1").Range("A12:AX14"), 3, 0.5, 1)
    End With
End Sub

Ideally, this function would display an array "rng" in Locals, such that rng(1) and rng(2) would remain unchanged and rng(3) would be added or multiplied by "x".

Are you trying to multiply the values of that row with `x`, or the row number itself? — Tim Stack, Jul 18 '19 at 07:44

Tim Stack · Accepted Answer · 2019-07-18T08:13:39.947

1

See if this helps

Option Explicit

Function shiftMatrix(rg As Range, row_number As Long, x As Double, method As Long) As Variant
Dim rng As Variant
Dim i As Long

rng = rg.value

If method = 1 Then
    For i = 1 To UBound(rng, 2)
        If IsNumeric(rng(row_number, i)) Then rng(row_number, i) = rng(row_number, i) * x
    Next i
ElseIf method = 2 Then
    For i = 1 To UBound(rng, 2)
        If IsNumeric(rng(row_number, i)) Then rng(row_number, i) = rng(row_number, i) + x
    Next i
End If

shiftMatrix = rng
End Function

rng is a two-dimensional array that holds the information from your rg range variable. I have created a loop that goes through the specified record (row) in that array and updates (either multiplies or adds) those values. Once that loop is completed, you return the array to the shiftMatrix array and after that you can just fill a range with that array again.

Two-dimensional arrays constructed from ranges are base 1, whereas the standard for regular arrays is base 0.

In your code, you treated the array as if it was still a range object. It's quicker to edit a range en masse in an array and later parse that information back into the range, rather than editing the Excel range directly.

edited Jul 18 '19 at 08:13

answered Jul 18 '19 at 07:50

Tim Stack

3,209
3
18
39

Works until the first ```Next i```. When I press F8 there, it jumps back to ```rng(row_number, i) = rng(row_number, i) * x ``` and I cant run next lines. – Julia Jul 18 '19 at 07:59
None. The yellow highlighted part just jumps up to the previous line of the code instead of executing the latter lines – Julia Jul 18 '19 at 08:04
1

That almost seems like [a ghost breakpoint](https://stackoverflow.com/a/40343078/10540017) – Tim Stack Jul 18 '19 at 08:05
Do you perhaps know how can I fix it? If it works on your side, isnt the problem maybe in the sub? – Julia Jul 18 '19 at 08:08
The fix is in the link. If it's not working for *you*, then the problem is due to the information you're feeding it. For one, problems can be caused by trying to feed the function cells with text, as those are rather hard to perform mathematical calculations on – Tim Stack Jul 18 '19 at 08:09
Ok thank you. Just fyi, when I changed method to 2, it worked fine. – Julia Jul 18 '19 at 08:11
Glad to see it works. I have edited the code so it won't break on non-numeric values – Tim Stack Jul 18 '19 at 08:13

How to multiply whole row in a range by number

1 Answers1