PDA

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



GregSydney
06-24-2020, 07:21 PM
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
06-25-2020, 02:31 AM
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
06-25-2020, 07:58 AM
Sheets("Data Entry").Range("C19")= "=indirect('Data Entry'!A19)"

GregSydney
06-25-2020, 03:38 PM
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
06-26-2020, 04:04 AM
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
06-27-2020, 05:23 PM
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
06-28-2020, 02:52 AM
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
06-29-2020, 01:07 AM
Correct, we want to stop the loop at the first empty cell.

Jan Karel Pieterse
06-30-2020, 05:03 AM
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