CODEWITHSUNDEEP
excelvbafunction
AL92
AL92

Reputation: 45

Pass .Range as a parameter VBA

Hi Please help me with the below, 

Sub movedata()

    Call select_data(.Range("B6:B12"))


End Sub


Function select_data(C As Range)

    Worksheets("sheet1").Range("I6:I16") = Worksheets("Sheet1").Range(C).Value

End Function

I can't see where I am going wrong,

Thanks,

Upvotes: 3

Views: 14052

Answers (2)

user4039065
user4039065

Reputation:

You are confusing the range as a Range object with its Address property.

Method 1:

Sub movedata()
    Call select_data(Range("B6:B12").address)
End Sub

Function select_data(C As string)
    with Worksheets("sheet1")
        .Range("I6:I16") = .Range(C).Value
    end with
End Function

Method 2:

Sub movedata()
    Call select_data(Worksheets("sheet1").Range("B6:B12"))
End Sub

Function select_data(C As range)
    C.parent.Range("I6").resize(C.rows.count, C.columns.count) = C.Value
End Function

fwiw, there is going to be some difficulties stuffing B6:B12's values into I6:I16's cells. There seem to be 4 missing values.

Upvotes: 0

Bond
Bond

Reputation: 16321

C already is a Range object so there's no need to pass it to the Range() function (which expects a string anyway) to create it.

Change:

Worksheets("sheet1").Range("I6:I16") = Worksheets("Sheet1").Range(C).Value

To:

Worksheets("sheet1").Range("I6:I16") = C

Upvotes: 2

Related Questions