Application Filling without formatting or promoting

[fjober]

Hello friends

I am using the following code to run a regression analysis, it works fine but has 2 problems:

1) It overwrites the cells format (I want to keep cells style format)

2) It promotes me about replacing the content of cells (I have 55 tests and can't click "ok" 55 times)

thanks in advance

Please Login or Register  to view this content.

[ajryan88]

Hi,

1) I don't know the answer here, as I've never used the regression analysis add-in from VBA sorry, but hopefully someone can chip in here and help you out.

2) Insert

Please Login or Register  to view this content.

before your Application.Run line, and

Please Login or Register  to view this content.

after it.

I hope this helps

[fjober]

Thank you ajryan88 for the replay

I have used it, and used another property, the AlertBeforeOverwriting both did not work :/ here is my full code

Please Login or Register  to view this content.

[ajryan88]

Sorry, that was my only suggestion...

I hate to say it here, because it doesn't help you at all, but I'm out of my depth on this one, and I'm going to have to bow out.

Good luck, and I hope someone can help you out in the near future.

Thanks

[fjober]

Thanks anyway .... as a temporal solution, I will clear cells before filling

[MrShorty]

One thought: the data regression tool simply uses the LINEST() function to perform the regression (http://office.microsoft.com/en-us/ex...842.aspx?CTT=1). So, my first thought is to bypass the data regressioin tool and call the LINEST() function directly: http://office.microsoft.com/en-us/ex...in=HA010277524

From there, a lot will depend on exactly what you need to do with this analysis and how will it fit into your overall project. You may use the VBA once to put the LINEST() function in the worksheet, or maybe store the results of the LINEST() function in an array in VBA and process it from there, or maybe even find that VBA is not needed at all.

[fjober]

Hello MrShorty

I need to use VBA because I will give the workbook to another user, he just want to insert data and then do ONE CLICK only

[MrShorty]

I will have to take your word for it. One of the first things I learned about spreadsheets is that, when they are designed correctly and only using worksheet functions, the formulas will update automatically as soon as date is inserted. So, a solution that does not use VBA, but uses the LINEST() function directly in the spreadsheet, your user may not even need one click- just insert the data and the analysis is done automatically. Again, it depends a lot on exactly how the data and the spreadsheet are structured.

In either case, I would still recommend looking at using the LINEST() function directly rather than using the regression tool.

[fjober]

mmmmm, well

My data are about security exchange, every month we will add the data of stock prices, this means more rows are added monthly to my sheet .... and the number of rows may be different from the previous month

anyway, I may rethink of my sheet structure Thank you all

[MrShorty]

Consider this discussion (and linked discussions) from earlier today: http://www.excelforum.com/excel-form...-set-size.html