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

 

[Joe4]

What are you ultimately using this "LabelRange" variable for?
I may have some other ideas on how to accomplish what you want.

 

[BuRnZz]

What are you ultimately using this "LabelRange" variable for?
I may have some other ideas on how to accomplish what you want.

The LabelRange is one of the ranges that I need to populate a chart with data
In the Chart-Sub it combines this range with others for the chart

Chart.SetSourceData Source:=Application.Union(LabelRange, ValueRange, Xwerte)

 

[Joe4]

Where exactly are you setting the "rownumber" variable value in your code?
I am not seeing it set anywhere in the original code you posted.

 

[BuRnZz]

I have

rownumber = 5

above but it later changes multiple times to different rows, i think I cant explain it all here but its a dynamic long value that gives me the row# which can be different each time :P

 

[Fluff]

How about

Set LabelRange = Union(TPsheet.Cells(rownumber, "C"), TPsheet.Cells(rownumber, "E"), TPsheet.Cells(rownumber, "G"), TPsheet.Cells(rownumber, "I"))

 

[Joe4]

OK, in the code you posted, you had "I = 5", not "rownumber = 5", which is why I was questioning it.
You had not set the value of "rownumber" in between the time you declared it and were first using it (unless you cut out a bunch of VBA code in the middle of this block):

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"))

Assuming you have the rownumber set properly, you can use UNION to combine those non-contguous cells to your range, like fluff showed above.

 

[BuRnZz]

How about

Set LabelRange = Union(TPsheet.Cells(rownumber, "C"), TPsheet.Cells(rownumber, "E"), TPsheet.Cells(rownumber, "G"), TPsheet.Cells(rownumber, "I"))

This is what I needed... Dont know why I couldnft figure this out myself. THANK YOU!! Works like a charm.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[BuRnZz]

Glad we could help & thanks for the feedback.

Nevermind
I still get "Application Defined or Object Defined Error" even on this line... Whats mysterious is that it works every 2nd time and throws an error every other 2nd time..
Im gonna go crazy haha