CODEWITHSUNDEEP
arraysvbafunction
Julia
Julia

Reputation: 261

How to multiply whole row in a range by number

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".

Upvotes: 0

Views: 50

Answers (1)

Tim Stack
Tim Stack

Reputation: 3248

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.

Upvotes: 1

Related Questions