Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FormulaArray (XP)

    Can one return the value from a FormulaArray when the FormulaArray is written via code?

    Example:
    ActiveCell.Offset(0.0).FormulaArray = "=XXXXXXXXXXX"


    The only alternative that I see is adding another line of code or two that will turn the results to a value.

    Thanks,
    John

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

    Re: FormulaArray (XP)

    The FormulaArray property is used to specify the formula for a cell as an array formula. It doesn't return the result of the formula - you have to look at the value of the cell for that, or perform the calculation in VBA.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FormulaArray (XP)

    Why is there an issue with another line of code:

    ActiveCell.FormulaArray = "=XXXXXXXXXXX"
    ActiveCell.value = activecell.Value

    Will calculate the formula and then replace it with the value...

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FormulaArray (XP)

    Thanks for your suggestions it is what I had thought. Although one never knows.....

    I have another question as it relates to variables being passed into the array formula.

    If I have a cell that has "01" in it and I pass it into an FormulaArray, the leading zero is removed and I am left with a 1. Same thing if the cell contained "04120", the leading zeros are removed. I would like to keep the leading zero.

    I have tried declaring a variable for the cell as a String and Variant and have had no luck.

    Regards,
    John

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

    Re: FormulaArray (XP)

    Can you explain in more detail what you are trying to do?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FormulaArray (XP)

    Like Hans, I am not exactly sure what you are doing, but I will make a "wild guess":

    If the variable is dimmed as one of the number types or a variant, it is not surprising that the leading zero will be removed since it is converted into a number before being used.

    If you put it into a variable dimmed as a string with a leading zero it will keep the leading zero.

    But it will also depend on what value is actually in the cell. If the cell is TEXT asn has "01" it will be fine in a string variable. If the cell has a number and just formatted to display with a leading zero, then you must add it yourself or use the TEXT property of the cell (eg instead of using something like <pre>myVar = Range("A1").value</pre>

    (which gives the value) you can use <pre>myVar = Range("A1").Text</pre>

    which stores what the cell display looks like.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FormulaArray (XP)

    Hans,

    I am trying to do a two-dimensional lookup using a FormulaArray. As an example Cell A1's contents is 01 and B1's contents is 07432. When I use code to generate the FormulaArray, the 01 and 07432 are stripped of the leading zero.

    As a work around, I created two variables and pass quote marks before and after the cell contents such as "01" and "07432" which then flows nicely into the FormulaArray.

    Regards,
    John

Posting Permissions

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