Solved: Using Offset w/ rng variable

[YellowLabPro]

Possible to refer to a object variable like this?
No savings here in this case.... but curious if possible, and how to write it if it is and I have it incorrect.

Set rng = ActiveSheet.Range("R4:R" & lrow)

Set rng2 = ActiveSheet.Offset(rng(1, 0))

[Charlize]

Not sure if I get it but I would use set rng2 = rng.offset(1,0)to add a row to the previous range. Or even better the resize parameter (now that I think of it)set rng2 = rng.resize(1,0)But I'm not sure which one. I'd go for the resize option.

[YellowLabPro]

Good Day Charliez,
Thanks-
Basically I am just wanting to use the first range as an index and setup an offset based on that.
I have commented the rng2 of the hardcoded values to show what I want the offset to use. The rng2 w/ the offset is not a valid instruction the way it is written.


Sub MissingDeptCat()
Dim c As Range, rng As Range, rng2 As Range
Dim lrow As Long
lrow = Cells(Rows.Count, 5).End(xlUp).Row
Set rng = ActiveSheet.Range("R4:R" & lrow)
'Set rng2 = ActiveSheet.Range("S4:S" & lrow)
Set rng2 = ActiveSheet.rng.Offset(1, 0)
For Each c In rng
If IsEmpty(c.Value) Then
c.Interior.Color = vbRed
c.Value = "Need Dept/Cat"
End If
Next c
For Each c In rng2
If IsEmpty(c.Value) Then
c.Interior.Color = vbYellow
c.Value = "Need Cat"
End If
Next c
For Each c In rng
If (c.Value) = "Need Dept/Cat" Then
MsgBox "Dept/Cat Missing"
End If
Exit Sub
Next c
End Sub

[Charlize]

Just noticed that you want to use an offset column. Use thisSet rng2 = rng.Offset(, 1)This will use the same number of rows in rng but with the values of the column next to it.

[tpoynton]

as another way of thinking about this, can you use range(cells(...)) as the format for specifying ranges (instead of letters)? I rarely find a need for using offset with columns, as I habitually specify ranges with numbers. then, you simply add your desired 'offset' to the column number...


I'm not saying one way is better than the other, just food for thought.

[YellowLabPro]

Thanks Charliez,
That did the trick. The offset(1,0) is the problem w/ dyslexia I have.

The interesting thing was that the reference to the ActiveSheet was the problem.
I guess that is saying ActiveSheet.ActiveSheet.rng and that is why I was getting the error Object does not support this property or method.

[YellowLabPro]

Hi Tim,
That is funny that you don't have a need for columns as much as rows, as mine is just the opposite. It is like most things I suppose, that most of us learn from what our surroundings and enviroment offer. That is a major reason I keep looking for new ways to do things I have learned, to keep options open for new things that will arise.

Cheers,