• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can somebody help me in making this formula dynamic in nature.

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear All,

I'm attaching my working file. Please go through the VBA code were in column C from C4 the formula is applied in only one cell. I would want to make my formula dynamic in nature so that the formula will work starting from C4 to infinite cells in column C.

Can somebody from the forum help me out. Please see my working code which is attached in my excel file.

Regards,

Sonjoe Joseph
 

Attachments

  • Calculation of Daily Returns.xlsm
    18.6 KB · Views: 28
jindon said:
What do you mean by "infinite cells"?
Click to expand...
I hope u have gone through my file. I want to make this formula dynamic in nature ie this formula has to work from C4 to down C5,C6 like this ie the no is end less. In simple terms the formula should be dynamic in nature. Everyday i will be entering the date and today closing price. In the third coloumn DR ie Daily Returns it has to calculate automatically.
 
Joe,
Nice to see u again
Below code will work for you,
but you can easily put formula in cell instead of VB code.
Just check


Code: 
Sub dailyreturn()
Dim A As Range
Dim B As Double
Dim C As Double


Set A = Worksheets("Sheet1").Range("C2").End(xlDown).Offset(1)

B = Worksheets("Sheet1").Range("B3").End(xlDown).Value
C = Worksheets("Sheet1").Range("b3").End(xlDown).Offset(-1).Value

A = (B - C) / C

End Sub
 
Change your code as shown below:

Code: 
Sub dailyreturn()

Dim c As Range
Dim LR As Integer
LR = Range("B" & Rows.Count).End(xlUp).Row

Application.EnableEvents = False

For Each c In Range(Cells(4, 3), Cells(LR, 3))
  c = (Range("B" & c.Row) - Range("B" & c.Row - 1)) / Range("B" & c.Row - 1)
Next c

Application.EnableEvents = True

End Sub
 
But the easier way is to put in C4: =(B4-B3)/B3
Then as you enter data simply press Ctrl+D when you move into C5 etc every day
 
Ashhu said:
Joe,
Nice to see u again
Below code will work for you,
but you can easily put formula in cell instead of VB code.
Just check


Code: 
Sub dailyreturn()
Dim A As Range
Dim B As Double
Dim C As Double


Set A = Worksheets("Sheet1").Range("C2").End(xlDown).Offset(1)

B = Worksheets("Sheet1").Range("B3").End(xlDown).Value
C = Worksheets("Sheet1").Range("b3").End(xlDown).Offset(-1).Value

A = (B - C) / C

End Sub
Click to expand...

Thank You Ashhu for helping me again. You are just my teacher and learning new things from you. Good day
 
Ashhu said:
Joe,
Nice to see u again
Below code will work for you,
but you can easily put formula in cell instead of VB code.
Just check


Code: 
Sub dailyreturn()
Dim A As Range
Dim B As Double
Dim C As Double


Set A = Worksheets("Sheet1").Range("C2").End(xlDown).Offset(1)

B = Worksheets("Sheet1").Range("B3").End(xlDown).Value
C = Worksheets("Sheet1").Range("b3").End(xlDown).Offset(-1).Value

A = (B - C) / C

End Sub
Click to expand...

Dear Ashhu,

I have checked your formula but its not working at all. I'm just getting errors. PLease do check out from your side what's the problem.
 
Hui said:
Change your code as shown below:

Code: 
Sub dailyreturn()

Dim c As Range
Dim LR As Integer
LR = Range("B" & Rows.Count).End(xlUp).Row

Application.EnableEvents = False

For Each c In Range(Cells(4, 3), Cells(LR, 3))
  c = (Range("B" & c.Row) - Range("B" & c.Row - 1)) / Range("B" & c.Row - 1)
Next c

Application.EnableEvents = True

End Sub
Click to expand...

Dear Sir,

I have checked your formula and that too is not working perfectly fine. Do check it from your side what's the problem.
 
Dear All,

After posting my question i have got three answers from Ashhu, Nebu & Hui. But when i have done the back testing it was Nebu Code who came perfectly fine. Good Job Nebu. Nebu can you explain the code for me since i didn't understand the code. My question is still open to anybody who can find a code which is simple and at the same time it should be dynamic in nature.
 
Joe
i am beginner to VB so I just put very simple code, As you said you enter fill B cell everyday and run this code, it works just ok.
error is because in your sheet C3 cell is blank, so its not recognizing.
I have put 0 (zero) in cell C3 and tested code, it works ok.

@Nebu code is excellent and intelligent and foolproof. I rather use that.
 
It gives me the same answer as you had and automatically extends to the extent of the data ?

SONJOE JOSEPH said:
Dear Sir,

I have checked your formula and that too is not working perfectly fine. Do check it from your side what's the problem.
Click to expand...
 
Hui said:
It gives me the same answer as you had and automatically extends to the extent of the data ?
Click to expand...

Hello Hui,

I was just working on your code with a new file. But things are not going fine since its showing errors. The screen short of the error and my working file attached. Please go through and awaiting for your feedback.

Regards,

Sonjoe Joseph
 

Attachments

  • err01.png
    err01.png
    258.9 KB · Views: 1
  • Workingfile.xlsm
    14.6 KB · Views: 1
Ashhu said:
Joe,
Nice to see u again
Below code will work for you,
but you can easily put formula in cell instead of VB code.
Just check


Code: 
Sub dailyreturn()
Dim A As Range
Dim B As Double
Dim C As Double


Set A = Worksheets("Sheet1").Range("C2").End(xlDown).Offset(1)

B = Worksheets("Sheet1").Range("B3").End(xlDown).Value
C = Worksheets("Sheet1").Range("b3").End(xlDown).Offset(-1).Value

A = (B - C) / C

End Sub
Click to expand...


Dear Ashhu,

Yes Ashhu the code is working perfectly fine. I checked right now. Good work.
 
SONJOE JOSEPH said:
Dear Ashhu,

Yes Ashhu the code is working perfectly fine. I checked right now. Good work.
Click to expand...

Ashhu,

Your code is working perfectly fine only thing every time when i open the excel file i will have to click the macros button every time. Is there a way even not clicking the macro button the formula has to work perfectly well. Just see the Nebu code his code is working perfectly fine. I don't even need to click the macros button everytime to run the formula. Just go through it and come up with your version of a simple code.
 
Just replace code in nebu sheet, it just runs fine. The instruction code in sheet 1 triggering this event when anything added or deleted in Cell B. Hope i am clear.
I am sure you understood how the scripts working now. please contact Forum for any further clarification.
Good Night
 
Nebu said:
Hi:

Find the attached.

Thanks
Click to expand...

Hai Nebu,

I was just working on yr code in a new excel file. But its showing me errors when i tried to do myself. Don't know what went wrong. Just attaching the screenshot and the working file. Do tell me what went wrong. Do check my codes in module as well as in sheet1
 

Attachments

  • err1.png
    err1.png
    231.9 KB · Views: 1
  • Stock Test01.xlsm
    16.2 KB · Views: 1
SONJOE JOSEPH said:
Hai Nebu,

I was just working on yr code in a new excel file. But its showing me errors when i tried to do myself. Don't know what went wrong. Just attaching the screenshot and the working file. Do tell me what went wrong. Do check my codes in module as well as in sheet1
Click to expand...

Hello Nebu,

I have noticed a problem on a particular line of code which is pasted below:-

dr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

If i simply paste this code from your file which u have send me it will work. But if i try to type this code by my own then it will not work. The problem is that when i start typing the code after we type (Rows.Count,1) when we enter a dot . this end list will come automatically which contains up, down etc etc. In my case that end list is not coming up. That is why that error is coming up. How to solve this do tell me?????
 
Ashhu said:
Just replace code in nebu sheet, it just runs fine. The instruction code in sheet 1 triggering this event when anything added or deleted in Cell B. Hope i am clear.
I am sure you understood how the scripts working now. please contact Forum for any further clarification.
Good Night
Click to expand...

Ashu,

Please advise why that error is coming.

Regards,

Sonjoe
 
Hi:

The error is basically because you have not declared the variable.
either you declare the variable using dim statement or use (dr&) , the code will work.

Thanks
 
Nebu said:
Hi:

The error is basically because you have not declared the variable.
either you declare the variable using dim statement or use (dr&) , the code will work.

Thanks
Click to expand...

Oh that's the reason. That is why Nebu has declared i$ in his statement. Thank You Ashhu for clearing my doubt. Thank You Thank You teacher
 
You must log in or register to reply here.
Back
Top