Pass .Range as a parameter VBA

Question

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,

Bond · Accepted Answer · 2015-08-19 11:26:03Z

2

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

answered Aug 19, 2015 at 11:26

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AL92 Over a year ago

Thank you, I'm still getting an error for .range("B6:B16") when I call secect_data.... Invalid or unqualified reference

Bond Over a year ago

You're using a . before .Range(...) which I assumed was because you had a With Sheets(...) block that you just weren't showing. If you want to pass a range from the active sheet, just remove the . when you pass your range. Otherwise, prefix it with your sheet name: Call select_data(Sheets("mysheet").Range("B6:B12")).

user4039065 · Accepted Answer · 2015-08-19 11:27:42Z

0

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.

answered Aug 19, 2015 at 11:27

user4039065

Collectives™ on Stack Overflow

Pass .Range as a parameter VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related