Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Morgantown, Pennsylvania, USA
    Thanked 0 Times in 0 Posts

    Including cell references in VBA code (2003)


    I'm hoping this is a simple one. I am creating a macro that does the following:
    1. Adds a reference sheet to the end of workbook
    2. Looks up information via a vlookup formula in cells on the preceding sheets.

    My problem:
    Since the lookup formula refers to a sheet that isn't there when the workbook opens, there are ref errors in the formula. So the formulas need to be created after the sheet is added to the back end of the workbook.

    So far, I've tried to write code to add the sheet to the back end of the workbook, and then more code to put the vlookup formulas into the proper cells after adding the sheet. Even though the macro was created while under the absolute reference switch, the Code line looks like this:

    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP('Cover Sheet'!R[-13]C[-3],ANCHORS!R[-11]C[-3]:R[335]C[3],3,FALSE)"

    Cover Sheet is the name of the worksheet that contains the lookup data, and Anchors is the name of the worksheet that contains the table. Now, it works, but using relative references seems kind of fragile, and I'd like the line of code to look like this instead:

    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP('Cover Sheet'!B2,ANCHORS!B4:H350,3,FALSE)"

    But when I put this in the code, my formulas come back as errors, because Excel puts in a bunch of extra single quotes in the formula:

    =VLOOKUP('Cover Sheet'!'B2',ANCHORS!'B4':'H350',3,FALSE)

    How can I stop Excel from putting in the extra quotes, or is there a better way to do this? Thanks for all help provided.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Including cell references in VBA code (2003)

    Excel has two ways of referring to ranges:
    1) The default A1 style, for example B3 or D2:G5 (relative), and $B$2 or $D$2:$G$5 (absolute)
    2) The R1C1 style, for example R[3]C[2] or R[2]C[4]:R[5]C[7] (relative), and R3C2 or R2C4:R5C7 (absolute)
    You attempt to set FormulaR1C1 (which expects an R1C1 style reference) to an A1-style formula. This won't work. Instead of FormulaR1C1, use Formula.

Posting Permissions

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