Excel VBA - trying to reference values of a range using cells

[Kingwi11y]

I am trying to set the values of cells in a range in the current worksheet

• to be the values of an equivalent range from another worksheet
• using .range(cells... since the range size is variable

This works perfectly:

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Range("A2:A22").Value

This fills all cells in the range with the value of the first cell in the reference range:

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Cells(2, "A").Value

But these do not work:

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Range(Cells(2, "A"), Cells(22, "A")).Value
Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Range(Cells(2, "A"), Cells(ListSize, "A")).Value

I get "Run-time error 1004 - Application-defined or object defined error"

I am a little stumped and I could do with some help please Many thanks

 

[Kingwi11y]

I referenced the worksheet within the range function for each cells and it fixed it:

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Range(Worksheets("Staff list").Cells(2, "A"), Worksheets("Staff list").Cells(22, "A")).Value

 

[Alex Blakenburg]

Typically you use a method that guarantees the sizes on the 2 sides will match. Why are using ListSize on the left but 22 on the right ?

 

[Akuini]

I think you can simplify your code like this:

With Worksheets("Staff list").Range("A2:A22")
     Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[Kingwi11y]

Typically you use a method that guarantees the sizes on the 2 sides will match. Why are using ListSize on the left but 22 on the right ?

Hi - sorry I should have clarified that.
I took ListSize out of the right side and replaced it with 22 when troubleshooting in case my referencing it was part of the issue.
Once the referencing was working, I put ListSize back in.

 

[Alex Blakenburg]

You are using ListSize as a row number, the name suggests it's a number of rows.

If its a row number you can change Akuini's suggestion to:

With Worksheets("Staff list").Range("A2:A" & ListSize)
     Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

If it is a number of rows you can change it to:

With Worksheets("Staff list").Range("A2").Resize(ListSize)
     Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[Kingwi11y]

That's brilliant guys
Thank you so much for this
I already had the rows.count in my ListSize variable
So I simplified it down to:

   With Worksheets("Staff list").Range("A2").Resize(ListSize)
        Range("A2").Resize(ListSize).Value = .Value
   End With

Lovely and neat - thanks

 

[Alex Blakenburg]

Perfect . Glad we could help.