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

1. ## 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. ## 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. ## Re: Macro and formula with variable

Originally Posted by kweaver
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. ## 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. ## 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),"")

6. ## Re: Macro and formula with variable

Originally Posted by kweaver
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. ## 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. ## Re: Macro and formula with variable

Originally Posted by kweaver
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. ## 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. ## Re: Macro and formula with variable

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

## User Tag List

#### Posting Permissions

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