Cannot resolving error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

0
votes

I am trying to print the output of some other functions to a new sheet in Excel using the following code:

Function WriteArrayToOutput(ByRef hour_array As Variant, value_type As String, my_month As String, day_type As String, current_row As Integer)
    Dim i As Integer
    
    With Sheet6
        .Range(current_row, 3).Value = my_month
        .Range(current_row, 4).Value = day_type
        .Range(current_row, 5).Value = value_type
        
        For i = 1 To 24
            .Range(current_row, i + 5).Value = hour_array(i)
        Next i
    End With

End Function

Every time I try to run the code I get the error

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

and debug points to the line .Range(current_row, 3).Value = my_month. I cannot seem to figure out how to fix this. Does anyone have a solution?

1
excelvba
nevermind. You want .Cells not .Range – Scott Craner
And since you are not returning anything to the the caller, this can be a sub instead of a function. – Scott Craner
You can use Resize and avoid the For i loop. – BigBen
^^^^: .Cells(current_row, 6).Resize(,24).Value = hour_array – Scott Craner

1 Answers

1
votes

Putting the comments into an answer:

With Sheet6
    .Cells(current_row, 3).Value = my_month
    .Cells(current_row, 4).Value = day_type
    .Cells(current_row, 5).Value = value_type
    .Cells(current_row, 6).Resize(,24).Value = hour_array
End With