Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create formula using VBA referencing changing cell

    I set up some code to pull values from a daily workbook to a summary one using VBA, and it was working perfectly for awhile. My customer now has taken to adding rows within the daily sheet causing the cell reference to be inconsistent when I try to pull the information across to the summary sheet. Due to the nature of the business, I can't stop him from adding rows. Instead, I need a way of determining where the information is. I found a snippet of code to determine the last active row in the daily workbook, and it works well. From there, I can determine the row that has the information I need. How do I incorporate this into a formula, using VBA, to pull the data to the summary sheet? The column will not change. I'm currently using the following statement: ActiveCell.FormulaR1C1 = "='[" & strName & "]Sheet1'!R[+41]C3" This works well with a predictable cell address, but not so well once it changes. I've found I'm unable to replace the +41 with a variable.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create formula using VBA referencing changing cell

    You can use something like this:

    ActiveCell.FormulaR1C1 = "='[" & strName & "]Sheet1'!R[+" & MyVariable & "]C3"

    for a relative reference, or

    ActiveCell.FormulaR1C1 = "='[" & strName & "]Sheet1'!R" & MyVariable & "C3"

    for an absolute reference. You will have to declare and set MyVariable before this.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create formula using VBA referencing changing

    I'm impressed! It works!

    Thanks for your amazingly quick reply!

Posting Permissions

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