Help needed simplifying code as procedure to long

Hi all

Could you please help?

I need to update 10 tables/matrix (pls see attached) based on cell values from the table which feed into sheet1 and then sheet2 will then provide the value that needs to go into the cell.

Being a novice I googled some solutions but decided to go with the basic/simple/noob friendly option below. The problem is that the code for all of the 10 tables makes it too long for a sub.

Is there a way of simplifying the code?

I hope this makes sense.

Thanks in advance

Please Login or Register  to view this content.

I do not know the reason for keep copying in to D9, one version

Please Login or Register  to view this content.

hi johnny_p,

there is no code in the posted file. The posted code looks a bit weird in all lines starting with Worksheets("sheet1").Range("D9").Value. In all the nine lines you overwrite the value in sheet 1 D9 with a new value from sheet 2: firstly it equals to A17 then A18 then A19.

In order to get help you need to provide instructions for all cell values in question.

Thanks for taking the time to look

I have uploaded the file again with the code.

In the basic sense I have to keep copying the cell values within each year/matrix to sheet one where a formula will then produce the output which needs placing into sheet2.

For example

yr1 Matrix - First box = Cell B17

Sheet2 B16 to go to Sheet1 D11 Then
Sheet2 A17 to go to Sheet1 D9

This gives me a figure in sheet1 I9 which needs to go to B17.

I then need it to work out the rest of the matrix going down from A17-A25 and across from B16-K16.

Then the same again for the rest of the years.

I hope this makes sense. I can confuse myself most of the time.

Thanks again in advance

the sheet1 is empty. As far as I understand you transfer two values there to use them in calculation. Can you show working workbook with calculation on sheet1 and fill a couple of value in sheet2 Year1?

Unfortunately the calculations also depend on loads of other cells in other sheets so no real point in putting it in. The values are all numbers

Thanks

This works but for only the range of of (A17:A25)

How would I get it to then look at the next cell in Range (B16:K16) and put this in Sheet1 (D11)? And then start over again with your code but offset the cell to the next column i.e "C".

Thanks again

Originally Posted by Izandol

You may try:

Please Login or Register  to view this content.

Sorry for any confusion.

They would need to be offset (0,1) from rgCell.value until column K.

Like a matrix, i,e sheet2 Cell E17 would be calculated from the inputs from E14 & A17.

Boom, Works perfectly! Thank you

Could you please don me one more favour. Could you please explain this line of code so that I can add other tables/matrix?

Please Login or Register  to view this content.

Thinking of adding another sub for each matrix and call upon each on when needed. Obviously each matrix has different cell inputs

I would be telling fibs if I said I fully understand. I'm half way there.

I didn't realise that this works out the whole 10 tables. Thanks for this.

I need to add another input to identify each table which will effect the outputs. Year table equates a year so 1-10.

Would it be best to say each time you loop "Next N" change cell value to match the "N" loop number? Does this make sense?

Sorted.

Thank you very much for your time, effort and skills.