Passing a Cell In a Range as An Object

JStellato

Board Regular
Joined
Nov 6, 2010
Messages
55
I'm trying to refactor some code, and got stuck when trying to refactor a section that works on cells

Code:
Public Sub MainSub ()

     MyArray = Range([A1], [B10])

     For lngRow = 1 To UBound(MyArray, 1)

[COLOR=#008000]          ' Currently I can do this with no problem
          ' Cell.Offset(, 6).AddComment
          ' Cell.Offset(, 6).Comment.Text = "Comment"
          ' What I want to do is [/COLOR]

          Refactored(Cell.Offset(, 6))

     Next

End Sub

Public Sub Refactored(Cell as Cell)
     Cell.AddComment
     Cell.Comment.Text = "Comment"
End Sub

Is this possible?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,785
In MainSub, the variable Cell isn't assigned a value when you call Refactored, hence an error.
 

JStellato

Board Regular
Joined
Nov 6, 2010
Messages
55
I don't quite understand, I just want to pass the cell I'm working with, in this case the cell does exist....

EDIT

Ok I see what you are saying, I sanitized this, that's why. However with the variable cell assigned I still can't refactor this.
 
Last edited:

JStellato

Board Regular
Joined
Nov 6, 2010
Messages
55
I'm trying to refactor some code, and got stuck when trying to refactor a section that works on cells

Code:
Public Sub MainSub ()

     MyArray = Range([A1], [B10])

     For lngRow = 1 To UBound(MyArray, 1)

[COLOR=#008000]          ' Currently I can do this with no problem
          ' Cell.Offset(, 6).AddComment
          ' Cell.Offset(, 6).Comment.Text = "Comment"
          ' What I want to do is [/COLOR]

          Refactored(Cell.Offset(lngRow, 6))

     Next

End Sub

Public Sub Refactored(Cell as Cell)
     Cell.AddComment
     Cell.Comment.Text = "Comment"
End Sub

Is this possible?

Fixed missing variable, but refactoring this still fails
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

Hi there,

Put 'Option Explicit' at the very top of the module. This will cause errors to show right away.

For instance: Public Sub Refactored(Cell as Cell) will raise a 'User defined type not defined' error, as Cell is not defined. In this case, you wanted as Range.

Hope that helps,

Mark
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,785
In MainSub,
Cell.Offset(lngRow,6) is the cell lngRow down and 6 to the right of the cell referened by the variable Cell.


If you want the cell in row lngRow of column F, the syntax would be Cells(lngRow, 6)
Perhaps
Code:
Public Sub MainSub ()

     MyArray = Range([A1], [B10])

     For lngRow = 1 To UBound(MyArray, 1)

          Call Refactored(ActiveSheet.Cells(lngRow, 6))

     Next

End Sub
 

JStellato

Board Regular
Joined
Nov 6, 2010
Messages
55
Thanks for the input, I did get this working because you put me on the right track. Here is the working code

Code:
Public Sub MainSub()

Set DataRange = Range(Cells(1, 1), Cells(10, 1))

For Each Cell In DataRange

     Call Refactored(Cell.Offset(, 6))

Next

End Sub


Public Sub Refactored(cellObj As Range)
     cellObj.AddComment
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,818
Members
409,838
Latest member
Cowells01
Top