Set Range = Worksheet.Range(Cells(..), Cells(..)) SYNTAX

[BuRnZz]

Hey Mr Excel Members

I keep getting an error ("wrong number of arguments" or "method range failed for object _worksheet") on trying to add multiple different cells from different rows (row as long that can change) to a range.

Dim TPsheet As Worksheet
Set TPsheet = ThisWorkbook.Worksheets("TP")

Dim rownumber as Long
I = 5

Dim LabelRange as Range
Set LabelRange = TPsheet.Range(TPsheet.Cells(rownumber, "C"), TPsheet.Cells(rownumber, "E"), TPsheet.Cells(rownumber, "G"), TPsheet.Cells(rownumber, "I"))

Im just trying to make the LabelRange different values, depending on different rownumbers. I used to go with

Set LabelRange = TPsheet.Range("C" & CStr(rownumber) & "," & "E" & CStr(rownumber) & "," & "G" & CStr(rownumber) & "," & "I" & CStr(rownumber))

but that throws errors occasionally too..

Im not sure what Im doing wrong, any help would be greatly appreciated...
Already read through this: Worksheet().Range(Cell(), Cell()) syntax?
and it didnt work

 

[Fluff]

When you get the error check what the value of Rownumber is.

 

[Joe4]

If you cannot figure it out, it might be best to post the WHOLE code, and a sampling of what your data looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BuRnZz]

When you get the error check what the value of Rownumber is.

Yup this was it, it was setting it to 0 at the end of the loop. Implementing "if rowvalue = 0 then exit sub" solved it.
Thanks!

 

[Fluff]

Glad it's sorted & thanks for the feedback.