Writing a worksheetFunction to the worksheet
Results 1 to 4 of 4

Thread: Writing a worksheetFunction to the worksheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    500
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Writing a worksheetFunction to the worksheet

    I m trying to populate a cell with the text


    =STEYX(J13:J26;K13:K26)

    My code below writes that into the Avar (Dimmed as a Variant)



    Avar = "=STEYX(" & Split(Cells(1, i).Address, "$")(1) & Trim(Str(FirstRowResult)) & ":" & Split(Cells(1, i).Address, "$")(1) & Trim(Str(LastRowResult)) & ";" & Split(Cells(1, ii).Address, "$")(1) & Trim(Str(FirstRowResult)) & ":" & Split(Cells(1, ii).Address, "$")(1) & Trim(Str(LastRowResult)) & ")"



    The code below - gives an error

    .Cells(Along, ii) = Avar


    If I use the code blow- it works - but only the calculated value is shown - not how it is calculated (I want it to show what range is used) - I have populated the two arrays with the values from row 13 to 26 for column K & L

    Application.WorksheetFunction.StEyx(RefVal, PredVal)
    xl2007 - Windows-7 & XP

    Does xl hate the number 255 ?
    biggest limitation - drives me insane

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,791
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Writing a worksheetFunction to the worksheet

    What is the resulting string assigned to Avar?

    Also, does your version of Excel use a semi-colon as a list separator, as per your example? Or does it actually use a comma as a list separator?

  3. #3
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    817
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Writing a worksheetFunction to the worksheet

    The way I solve problems with writing formula into cells is to remove the "=" from the front of the formula so that you just write Text. then go to the cell and manually put the "=" back in , EXCEL then very kindly highlights where the error is.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  4. #4
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    500
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Writing a worksheetFunction to the worksheet

    Quote Originally Posted by Domenic View Post
    What is the resulting string assigned to Avar?

    Also, does your version of Excel use a semi-colon as a list separator, as per your example? Or does it actually use a comma as a list separator?


    Avar=
    =STEYX(J13:J26;K13:K26)

    If I add a ' (remark) - then goto to the cell where it wrote

    '=STEYX(J13:J26;K13:K26)

    and remove the ' ---- then it works



    xl2007 - Windows-7 & XP

    Does xl hate the number 255 ?
    biggest limitation - drives me insane

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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