Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Macro and formula with variable
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro and formula with variable

    Hi, I wish I could have your help to solve what is a "big problem" for me in VBA.
    Let me try to explain.
    I have in column A a series of number, no blank cells. This is a dynamic serie, adding new data every day.
    In cell C1 I have a variable, a number that I choose.
    How can I get a formula in column B, in every cell starting from B1 to the row that depends on the number I set in C1 (call it B#), where in B# is the LN (natural logaritm) of the last value of column A. In the previous cell there will be the LN of the second to last value of column A, and so on.
    Thank you
    Ciao a tutti.
    M

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    I think I understand this, but it would help if you posted some actual numbers and the results you want.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    Quote Originally Posted by kweaver View Post
    I think I understand this, but it would help if you posted some actual numbers and the results you want.
    Hi kweaver,
    first of all I want to thank you. Please find below an easy sample. The target is to automate the file so that, changing cell C1 (5 in this example), I don't have to do all the calculations manually.
    Ciao
    M
    21200 =LN(A6) 5
    21205 =LN(A7)
    21200 =LN(A8)
    21210 =LN(A9)
    21215 =LN(A10)
    21220
    21210
    21200
    21975
    21985



  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    Is 21200 in A6, etc. or does 21200 start in A1?

    Where does the 5 come from? It's not the natural log of 21200 nor 21220

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    Does the 5 mean you want 5 natural logs calculated starting at A6?

    If so, put this in B6 and fill down: =IF((ROW()-5)<=$C$6,LN(A6),"")
    Last edited by kweaver; Sep 11th, 2019 at 08:28 AM.

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    Quote Originally Posted by kweaver View Post
    Does the 5 mean you want 5 natural logs calculated starting at A6?

    If so, put this in B6 and fill down: =IF((ROW()-5)<=$C$6,LN(A6),"")

    Hi kweaver, in the example above every cell starts at row 1. The problem is that column A is dynamic: I have new data to add every day. So the formula should find the very last data of column A and starting at the row I have set in C1 (5 in the example, but it could be 20... 100... ect...) must fill all the rows above (so if it start at row 5 with the LN of the very last value in col A, row 4 has the LN of the second to last value of col A and so on...).
    Thank you
    Ciao
    M

  7. #7
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    If this is B1: =IF(ROW()<=$C$1,LN(A1),"")
    And the 5 (or whatever number) is in C1, filling down the B1 formula will create as many LN instructions as C1 indicated.

    Excel 2010
    ABC
    1212009.961756466
    2212059.96199228
    3212009.96175646
    4212109.96222805
    5212159.96246376
    6212209.96269941
    721210
    821200
    921975
    1021985

    Sheet25



    Worksheet Formulas
    CellFormula
    B1=IF(ROW()<=$C$1,LN(A1),"")


  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    Quote Originally Posted by kweaver View Post
    If this is B1: =IF(ROW()<=$C$1,LN(A1),"")
    And the 5 (or whatever number) is in C1, filling down the B1 formula will create as many LN instructions as C1 indicated.

    Excel 2010
    A B C
    1 21200 9.96175646 6
    2 21205 9.96199228
    3 21200 9.96175646
    4 21210 9.96222805
    5 21215 9.96246376
    6 21220 9.96269941
    7 21210
    8 21200
    9 21975
    10 21985
    Sheet25

    Worksheet Formulas
    Cell Formula
    B1 =IF(ROW()<=$C$1,LN(A1),"")
    Hi Kweaver, that doesn't work because in B1 the LN is not the one of the value in A1 (21200 in your sheet) but must be of row 5 (21215). In cell B6 the LN of the last row of col. A (21985), in B5 of 21975, in B4 of 21200, and so on. As I wrote the problem are: 1) I add new data every day to col. A; 2) I have to change the value in C1.
    I really appreciate your hint.
    Thank you,
    Ciao
    M

  9. #9
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    When C1 is a 5, you want the value in B1 to be the natural log of the value in A5, then the LN(A6), etc. through until there's no more data in column A??
    So, if there were 11 rows in column A, B7 would be LN(A11) ? I hope I got it now.

    Kevin

    Excel 2010
    ABC
    1212009.9624637585
    2212059.962699412
    3212009.962228048
    4212109.961756461
    5212159.997660723
    6212209.998115682
    721210
    821200
    921975
    1021985
    11

    LN



    Worksheet Formulas
    CellFormula
    B1=IFERROR(LN(INDEX(A:A,$C$1+ROW()-1)),"")



    AND

    Excel 2010
    ABC
    1212009.9624637585
    2212059.962699412
    3212009.962228048
    4212109.961756461
    5212159.997660723
    6212209.998115682
    7212109.952515784
    821200
    921975
    1021985
    1121005

    LN



    Worksheet Formulas
    CellFormula
    B1=IFERROR(LN(INDEX(A:A,$C$1+ROW()-1)),"")


  10. #10
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro and formula with variable

    Thank You!!!
    It works the way I was looking for.
    You really got it.
    Grazie
    Ciao
    M

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
  •