0

How would I loop through two ranges using an integer, like this:

Dim column1 As Range
Set column1=.Range("A1:A1000")
Dim column2 As Range
Set column2=.Range("B1:B1000")

Dim i As Integer
For i=1 To column1.Height
 If column1.Cells(i,1).Value = someValue And column2.Cells(i,1).Value = someValue then
   ...
 End If
Next

Would this approach work? How do I access the cells of the stored ranges? Most questions and answers deal with the looping via For Each cycle, which is something I'd like to avoid.

Ans
  • 1,212
  • 1
  • 23
  • 51
  • 2
    Did you try it? What did not work? – Scott Craner Sep 06 '17 at 13:02
  • @Scott Craner, I didn't yet. I'm not well used to Range properties. Anyways I thought the quesiton and the answers might be useful to future users since there's little on topic here. – Ans Sep 06 '17 at 13:04
  • This loop seems right, doesn't seem to have syntax errors. – danieltakeshi Sep 06 '17 at 13:04
  • It is out there. and yes it works. – Scott Craner Sep 06 '17 at 13:04
  • @Scott Craner I had trouble finding it then. Sure there are other people like me. – Ans Sep 06 '17 at 13:05
  • 2
    Another method that assumes the Cells() property, since it is one column: `column1(i)` – Scott Craner Sep 06 '17 at 13:07
  • And this took me one search and it was the first option when I searched `Range.Cells`: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-cells-property-excel?f=255&MSPPError=-2147217396 – Scott Craner Sep 06 '17 at 13:09
  • 1
    dim i as **Long** !!!!! (unless you work with Excel 3.0 8-) – iDevlop Sep 06 '17 at 13:10
  • 1
    Ah, now it may not work - you've changed `1000` to `column1.Height`. Do you mean `column1.cells.count` instead? If a cell height is 15 and there's 1000 cells in the column then column height = 15000 (I think), while count of cells equals 1000. – Darren Bartrup-Cook Sep 06 '17 at 13:10
  • And here is an excellent one from stack overflow: https://stackoverflow.com/questions/25879619/whats-the-difference-between-range-item-and-range-cells – Scott Craner Sep 06 '17 at 13:10
  • You can also use Union for non contiguous Range and loop through it – danieltakeshi Sep 06 '17 at 13:12
  • @ScottCraner - I can't see the duplication. The linked question is talking about the difference between just `Cells(x,y)` and `Item(x,y)` while this is about looping through two ranges. – Darren Bartrup-Cook Sep 06 '17 at 13:16
  • It also explain explicitly how what you want to do works. The question might be different but the answer, answers your question. – Scott Craner Sep 06 '17 at 13:17
  • @Darren Bartrup-Cook I meant to get a number of rows in the Range. The `coutn` is the correct one, I suppose? – Ans Sep 06 '17 at 13:55
  • @ScottCraner Not my question. I have to disagree with you though and getting myself thoroughly confused trying to explain that. :) **Ans** - yes, you'll need the count of the cells rather than the height. – Darren Bartrup-Cook Sep 06 '17 at 14:02
  • @DarrenBartrup-Cook you have enough rep to vote to reopen. I was going off the fact that the OP wanted to know if the Range.Cells() property worked, Sidd's answer not only states it will, it explains the difference between Range.Cells() and Worksheets.Cells(). As to the other error of the count, it was not in there when I went looking for the duplicate. It was added after the fact. Also the OP stated that they wanted a place for others to find, and this will now direct them to a great instruction on that property. – Scott Craner Sep 06 '17 at 14:07
  • @ScottCraner. I was reading through the code when it changed to `column1.Height` and I was just pointing out to the OP what the result of that was (not the count of cells). I'm not actually sure what the OP was asking - I assumed it was just how to loop through a couple of ranges, with the 2nd range offset by 1 column (I'd use `For...Each` & `OFFSET` but the OP doesn't want that). Don't think I'll go as far as voting to re-open (that would involve me scrolling up and pressing a button!) if Ans has the Answer he's after. – Darren Bartrup-Cook Sep 06 '17 at 14:26
  • 1
    @DarrenBartrup-Cook and apparhently he does: https://stackoverflow.com/questions/46077208/multi-condition-search-function-doesnt-work#46077696 Which is a great question. – Scott Craner Sep 06 '17 at 14:29

0 Answers0