Reputation: 261
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
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