0

I am trying to loop through a cells in range using this piece of code. But it throws error in last statement given here

"Object doesn't support this property or method".

What is the alternative/solution to it. Thanks for your replies.

Dim Rng As Range
Set Rng = Range(Cells(1, 1), Cells(1, lastcolTTR))
Rng.Select

For Each ct In TTRfile.Rng.Cells
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73

1 Answers1

1

I do not know how TTRfile or ct are declared, but I know Rng is a Range which means TTRfile.Rng.Cells is a problem, if not the problem.

If ct is a variant or a range object then you can use For Each and a syntactically correct way of creating the loop is:

For Each ct In Rng.Cells

However, if ct is numerical, as could be the case with ct = Rng.Cells.Count then you cannot use For Eachand must use an incrementing/decrimenting index variable:

For i = 1 to ct

Also, you're implicitly using the ActiveWorksheet when you set Rng, you should get into the habit of explicitly qualifying the worksheet. If TTRfile is a worksheet then

Range(Cells(1, 1), Cells(1, lastcolTTR))

Would look like this:

TTRfile.Range(TTRfile.Cells(1, 1), TTRfile.Cells(1, lastcolTTR))

Or this:

With TTRfile
    .Range(.Cells(1,1), .Cells(1, lastcolTTR))
End With

Speaking of lastcolTTR, how is that declared? It can become a problem if it doesn't evaluate to a number. For example, if it is a variant then it becomes possible for it to hold a range and you will get an error if that range contains more than one cell. Also, be sure you catch any condition that would cause lastcolTTR to be less than 1 because Cells(1,0) will generate an out of range error.

ProfoundlyOblivious
  • 1,455
  • 1
  • 6
  • 12
  • Thanks very much for your reply and the valuable points. I understood where I was wrong. Corrected it now. Thanks again! It was simple. This is what worked for me For Each ct In Rng.Cells –  Jan 31 '20 at 03:48