Passing a Cell In a Range as An Object

[JStellato]

I'm trying to refactor some code, and got stuck when trying to refactor a section that works on cells

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?

 

[mikerickson]

In MainSub, the variable Cell isn't assigned a value when you call Refactored, hence an error.

 

[JStellato]

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.

 

[JStellato]

I'm trying to refactor some code, and got stuck when trying to refactor a section that works on cells

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]

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]

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

Public Sub MainSub ()

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

     For lngRow = 1 To UBound(MyArray, 1)

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

     Next

End Sub

 

[JStellato]

Thanks for the input, I did get this working because you put me on the right track. Here is the working 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