how to fix a macro

[candicee]

Hello everyone,

I'm very aware this is not a great time to ask for help, but if anyone if feeling charitable and has some free time on this blessed day, I'm in need of some help from a smarter person than me!

I have made this code, and I need to know if the syntax is bad, or if I'm justifying to do something I can't do:

Please Login or Register  to view this content.

[TMS]

You seem to be mixing up Cells syntax and Range syntax. You refer to a cell by Cells(row, column) or by Range(" columm and row "), for example, Range("A2").

[candicee]

ok ill look into that thank you!

do you think this could work if I fixed the cells and range syntax?

[candicee]

Is this what you mean?
it still won't work

Please Login or Register  to view this content.

[mehmetcik]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[xladept]

Nevermind

[candicee]

Thank you for the remark, I used the code tags!

[TMS]

This

Please Login or Register  to view this content.

should be this

Please Login or Register  to view this content.

[candicee]

thank you!

I changed it. There is still a problem with this line:

Please Login or Register  to view this content.

I thought it might be the "ligne -1", but I tried without and it still won't work.

[mehmetcik]

Please Login or Register  to view this content.

[jolivanes]

Do I read it right? 30600 loops? Seems a little high, even for the holiday season.

How did you arrive at 10192?
I think you could replace this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

Should be faster.
I have not checked it, going out the door momentarely, so be careful.

[candicee]

Do you mean that I did something wrong? I'm supposed to get a performance out of an index (Euro Stoxx) for a period of more than 20 years (day by day), hence the number of loop.
Do you think it can't work? excel has been crashing if I try to execute the macro but I thought it meant I typed something wrong!

[xladept]

See whether this runs:

Please Login or Register  to view this content.

[candicee]

thank you.

I tried and I get an error message and it highlights this line:

Please Login or Register  to view this content.

[xladept]

Oops!

Please Login or Register  to view this content.

[candicee]

xladept, thank you

EDIT: it works great!

only problem is the variables pa pae and mdd should come out as percentages, and so I have weird answers in my message box but I will try to fix that now!

[xladept]

You're welcome!

Maybe:

Please Login or Register  to view this content.

[candicee]

thanks, the problem is I would need to make a similar change to switching a cell in excel from "general" to "percentage", so that it changes the result as well, for example from 1 to 100%.


I have another question, would I be able to change that program so that a user can select one out of 5 index, and it shows the results of those things (performance, max drawdown..) only for the one they chose?

Like an input box that gives a result depending on what the user type in?

[xladept]

What do those values look like now?

I can't visualize the other question.

[candicee]

Basically I built this program around two columns: the dates and the values at each date of one index. In my original file I have 4 other column with the values by dates of 4 other indexes.

Originally, I planned to create a button activated macro for each index, (the program you helped me built).

But now I think I'd be better to have only one button that would bring up an input box that asks "enter the name of your index", and depending on what index the user typed in, the program would execute the calculations for the column pertaining to that index.

something like:
n=inputbox ("Choose one of the following index: EuroStoxx, CAC40.. etc")

if n = eurostoxx then
and here Ienter the program using the corresponding column
end if
if n = cac40 ... and so on and so on

[candicee]

For performance it shows 0,0037
for yearly performance it shows 0,9963811,
for maximum drawdown it shows -0,1767426

When I have done these calculations with excel only, the results were
226,40%
4,27%
-66,88%

so there are several mistakes along the way, but I think the format is one of them

[xladept]

Thanks for the rep candicee - I'll delve into it further tomorrow!

[xladept]

Hi Candicee,

I believe that I need a sample workbook so will you?:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[candicee]

I tried a couple time, and it won't upload my file! thanks anyways

[xladept]

Don't post the whole book - just a hundred or so rows