Sleeper: Copying a Value to an Addess Reference which is Nested in Another Cell

[GregSydney]

Hi There,

I'm trying to copy the value of a cell to another cell in another worksheet, using the address of the destination cell, which is itself nested as the value of another cell (this is an important part of the logic as the address is a variable):

The value I want to copy is located:

Sheets("Data Entry")
Range("C19").Select 

The destination address is:

Sheets("Source Ingredients")
Range("$DH$12")

The above destination address DH18, is the value inside another cell:

Sheets("Data Entry")

Range("A19").Select 

The value DH18 is variable

Any help would be greatly appreciated.

[Jan Karel Pieterse]

If I understood correctly this is your syntax:

Sheets("Source Ingredients").Range(Sheets("Data Entry").Range("A19").Value).Value = Sheets("Data Entry").Range("C19").Value

[snb]

Sheets("Data Entry").Range("C19")= "=indirect('Data Entry'!A19)"

[GregSydney]

This is working perfectly thanks Jan,

Now, if I want A19 to grow into a range, say A19:A26, (similarly, C19 becomes C19:C26),
And I want this ranges to grow dynamically according to the number of new rows I am adding to the table.
And keep the logic of the syntax you helped me with, applying to the new rows...

How about that?

[Jan Karel Pieterse]

Depends, if C19:C26 contains addresses which are in consecutive order (DH12 to DH19) then you can simply do this in one go

Sheets("Source Ingredients").Range(Sheets("Data Entry").Range("A19").Value).Value = Sheets("Data Entry").Range("C19:C26").Value

Where A19 contains DH12 : DH19.
If A19, 20, 21 contain DH12, DH13, DH4 and etcetera (or any other order) then you will have to loop through those cells:

Dim Cl as Range
For Each Cl in Sheets("Data Entry").Range("A19:A26")
    Sheets("Source Ingredients").Range(Cl.Value).Value = Cl.Offset(,2).Value  'This is two columns to the right of col A, so column C on the same row
Next

[GregSydney]

Great. Your second option is working as in deed, the DH are not in a consecutive order.

Lastly, and then it should work fine. How do I break the loop after the last cell of the range, to then go on with the rest of the code.

At the moment, I get a bug after the loop has gone through the whole range.

The number of loop performed must be related to the size of the range (e.g. A19:A26 is 5 loops, A19:A23 is 4 loops and so forth....)

To sum up:
If the loop has gone through the whole range
Then, break the loop and go on with the code

Cheers

[Jan Karel Pieterse]

Depends. How can we determine which cells to process? Should we just start at a certain cell and stop at the first empty cell?

[GregSydney]

Correct, we want to stop the loop at the first empty cell.

[Jan Karel Pieterse]

In that case you simply extend the number of rows to operate on and add one line:

Dim Cl as Range
For Each Cl in Sheets("Data Entry").Range("A19:A1000")
    If Cl.Value = "" Then Exit For 'Exits on first empty cell
    Sheets("Source Ingredients").Range(Cl.Value).Value = Cl.Offset(,2).Value  'This is two columns to the right of col A, so column C on the same row
Next