1

When using For Each cell in Range, I would like to use the cell.row property to refer to the row of the current cell. However if I use it in another Sub, I get the error Run-time error:'424' Object required.

Below is a simple code to illustrate what I mean. Am I doing anything wrong or is there a way to refer to the row of the current cell in another Sub? Thanks!

Sub CommandButton2_Click()

 Dim rng As Range
 Set rng = Range("P290:P293")

 For Each cell In rng
  Third
 Next cell

End Sub

Sub Third()

 MsgBox cell.row

End Sub
yingxuan
  • 95
  • 3
  • 6
  • 1
    You haven't declared `cell` in either procedure, so along with being implicitly `Variant` type, they're also scoped only at the procedure level, which means that even if the two procedures share the name `cell`, those two different `cell` are two different things. IN the case of `Third` procedure, `cell` is an undefined variable, an empty/nothing/null-string, which doesn't have a `.row` property. Proper scoping, passing variables, and above all **declaring** your variables will help you avoid these errors. – David Zemens Nov 03 '16 at 14:41
  • thanks! sorry cause this is the first time i am writing macro >. – yingxuan Nov 03 '16 at 16:03

1 Answers1

2
Sub CommandButton2_Click()

 Dim rng As Range, cell As Range
 Set rng = Range("P290:P293")

 For Each cell In rng
  Third cell
 Next cell

End Sub

Sub Third(cell As Range)

 MsgBox cell.row

End Sub

You need to pass it as an argument.

Graham
  • 7,431
  • 18
  • 59
  • 84
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • 1
    or make `cell` global variable using `Public cell As Range` – Vasily Nov 03 '16 at 14:39
  • @Vasily you could indeed - but in a loop you would have to `Set` that reference in each iteration which means more overhead. For code that isn't looping, using a public-scoped variable would be easier – SierraOscar Nov 03 '16 at 14:40
  • I don't think so, I think that time of `excel 95` is over, so it just on user own what he should use. my comment is just to add additional option... +1 from my side – Vasily Nov 03 '16 at 14:46
  • @Vasily I'm not disputing anything you're saying - it's a perfectly viable alternative. From a programming perspective though _in general_ I _personally_ would avoid setting an instance of an object within every iteration of a loop. In this example it's fine - but that might not be the case for every time someone comes across this problem. I'm talking generally rather than just VBA and Excel. You're absolutely right in what you're saying tohugh – SierraOscar Nov 03 '16 at 14:49
  • In terms of general approach ... agree with you – Vasily Nov 03 '16 at 14:54