+ Reply to Thread
Results 1 to 7 of 7

Trouble Using Last Row in VBA Formula

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    65

    Trouble Using Last Row in VBA Formula

    Hi,

    I am calculating probability density function for a set of data which is arranged over 55 rows in a column. The equation is working if I refer the absolute row positions in the formula, however it is not working if I try making it dynamic, by defining a last row. The data is from row 2 to 56. The mean and standard deviation values are placed at row number 58 and 59, so to calculate the probability function I need to refer the row number 58 and 59 in the formula. The trouble is I have to modify the formula everytime the number of data changes, sometimes 100 rows of data or sometimes 20 rows of data.

    How can I make it dynamic? Can you tell me a way to modify the NORMDIST formula to integrate the last row function? Anyone?

    Here is the code:
    Please Login or Register  to view this content.
    It'd help me alot! Thanks in advance.
    Sanjeev
    Last edited by sanjeevpandey; 03-12-2013 at 11:43 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Trouble Using Last Row in VBA Formula

    Edit: Self deleted as I didn't read the whole issue and need to re-evaluate.
    Last edited by alansidman; 03-12-2013 at 11:14 AM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Trouble Using Last Row in VBA Formula

    Thanks alansidman for replying.

    I already have declared last row and next row as long.

    I did not show the entire code because I only had problem in this part of the code.

    For example this part of the code is working with next row in the formula
    Please Login or Register  to view this content.
    I don't know how to reference rows and cells dynamically in Normdist formula.
    I just need a proper referencing I think. Instead of referencing RC way i have to use another method.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Trouble Using Last Row in VBA Formula

    Try this:
    Please Login or Register  to view this content.
    Alan

    Edit: Small tweak to make last row and nextRw absolute

    Please Login or Register  to view this content.
    Last edited by alansidman; 03-12-2013 at 11:34 AM.

  5. #5
    Registered User
    Join Date
    11-27-2012
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Trouble Using Last Row in VBA Formula

    a little trouble!

    It seems the position of Last row is changing each time the formula is written down the column in each cell.

    Why the last row size is not fixed and it changes as the formula goes down the column?

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Trouble Using Last Row in VBA Formula

    See my edit and previous thread which I posted as you were posting.

    Alan

  7. #7
    Registered User
    Join Date
    11-27-2012
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Trouble Using Last Row in VBA Formula

    Lovely! yup the second edit worked!

    Thanks alot genius!

    Have a wonderful day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1