CODEWITHSUNDEEP
vbaexcel
user8937713
user8937713

Reputation:

For loop with range of cells within another range

Trying to figure out what the code below is doing, but cannot figure it out:

For Each c In Sheets("Control").Range("y3:y" & Range("y" & 
Rows.Count).End(xlUp).Row).Cells

Sheets("forecast").Range("a5") = c

What I think is happening:

Below c is set to a certain cell, this contains a name. In the upper part for every cell, in sheet control, for the range from y3 till the end, .... and then the confusion starts. What happens next:

Range(".." & Range(".." & Rows.Count).End(x1Up).Row).Cells

How do I read this?

This is the table from Sheets("Control"):

enter image description here

Upvotes: 1

Views: 53

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

There are two ways. You can do this by importing it as a range object or by saving it as an array.

First: as Range

Sub test()
    Dim Ws As Worksheet
    Dim rngDB As Range, c As Range
    Set Ws = Sheets("Control")
    With Ws
        Set rngDB = .Range("y3", "y" & .Range("y" & Rows.Count).End(xlUp).Row)
    End With
        For Each c In rngDB
            Sheets("forecast").Range("a5") = c
            Sheets("forecast").Range("b5") = c.Offset(0, 1)

End Sub

Second: as Variant Array

Sub test2()
    Dim Ws As Worksheet
    Dim rngDB As Range, c As Range
    Dim vDB
    Dim i As Long
    Set Ws = Sheets("Control")
    With Ws
        Set rngDB = .Range("y3", "z" & .Range("y" & Rows.Count).End(xlUp).Row)
        vDB = rngDB '<~~ get data from rngdb to array vDB
    End With
        For i = 1 To UBound(vDB, 1)
            Sheets("forecast").Range("a" & i + 4) = vDB(i, 1)
            Sheets("forecast").Range("b" & i + 4) = vDB(i, 2)

End Sub

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37500

Range(".." & Rows.Count).End(x1Up).Row - you have a typo here, it is xlUp (l, for lemonade, instead of 1). Use Option Explicit to avoid such mistakes!

Anyhow, it means something like that: in column "..", in your case y, go to the very last row at the bottom. Then go up, until first non-blank cell is met and get its Row.

So if you have values in range Y1:Y20, then going up from the bottom will end in 20th row, giving you 20 as the row number.

Upvotes: 1

Related Questions