Macro and formula with variable

maz71

New Member
Joined
Sep 10, 2019
Messages
5
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I think I understand this, but it would help if you posted some actual numbers and the results you want.
 
Upvote 0
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

<tbody>
</tbody>


 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
Cell Formulas
RangeFormula
B1=IF(ROW()<=$C$1,LN(A1),"")
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet25

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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
 
Upvote 0
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
Cell Formulas
RangeFormula
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
Cell Formulas
RangeFormula
B1=IFERROR(LN(INDEX(A:A,$C$1+ROW()-1)),"")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top