Consulting

Results 1 to 9 of 9

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

  1. #1

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

    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.

  2. #2
    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
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,921

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


















































































  4. #4
    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?

  5. #5
    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
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  6. #6
    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

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

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

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

  9. #9
    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
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •