Hi All,

Basically I like building basic solver models and have attached a section of one in which, I have tried to model ratings for the Rugby Super League - somewhat unsuccessfully (or maybe this kind of modelling doesn't work for that). In short, I add data to feed the model (so to speak), make a few changes, run solver, get some new ratings etc. Basically I am looking to try and automate this process somewhat.

In the attached (I have tried to highlight everything involved red to help), I have taken rows 143-150 on sheet 1 (where I added last weeks results in), copied down my text functions in columns, L and M and S and T, and then pasted the values into columns C, D, E and F - from row 185 down on sheet 3. I have also copied the date across and re-formatted it from sheet 1, to column A on sheet 3. I have then copied down on sheet 3, columns, B and H to N from row 184 through 192.

I have then tweaked three values in row 46, D2, F2 and F3 and I1.

I have then copied the two blocks of 1's, into the cells I want Solver to calculate for me (as I just find that I seem to get cleaner values starting from scratch everytime as it doesn't always converge properly or takes forever and it seems quicker and cleaner to solve from effectively a constant rating everytime).

I then ran Solver.

This gave me a Macro of (the Solver element doesn't now seem to work properly - apologies I recorded this as I am not fluent in Macro and really don't know why )

Please Login or Register  to view this content.
This has given me a couple of thoughts:

Is actually possible to trigger Solver from a Macro?
Can I change the code so that the Macro will automatically figure, which cells to copy and paste from on sheet 1 and where to paste them on sheet 3? (I'm figuring I would need to do some irow business and pick an appropriate cell before running the Macro?)
Similarly the cells I'm changing on sheet 3 (particularly F2 which isn't critical but I1 is), have to account for the last row of data added (e.g. 192 in this case) - is this possible?
D2 (on sheet 3) has to be one more than the highest value in the last row of column B (e.g. D2 is 25 in this case as column B has a high vaklue of 24) - is this possible (I'm not overly worried about it for this particular model but would like to use this in something else I do)?
Can I make what I have done above tidier?

I think this covers everything that I was trying to do. Apologies for the slightly basic nature of this, I'm not particularly fluent when it comes to Macro's and was just wondering if I could make this process easier. Ultimately I'd like to put web queries into this so that I could make the process down to just a couple of clicks (but that can wait).

Thanks in advance for any suggestions and advice.

Rugby League_Super_League_2014_Unweighted_RevE_EFCopy_Test.xlsm