cell references help needed

help still needed

1) I think you need to create another variable, say k, to replace -30 - i because the RC reference won't accept a calculation. Then you can use

For i = 1 To M - 2
      k = -30 - i
   ActiveCell.Offset(0, 1 + i) = "=Input!R[" & i & "]C[" & k & "]"
Next i

Note that the text between the quotes is exactly what will appear in the spreadsheet so you don't want the i or k to be in quotes as you want their values to be shown instead.

2) Sorry I don't really understand what you're trying to do with

Pred = Pred + Solver!RC" & 31 + i & " * "Input!R" & 6 + i & "C[2]"

. Could you explain a bit more, please?

Thanks Huron, I'll try your advice. Will R[" & i & "] work, whereas R[i] won't?

What I am trying to do with the Pred = Pred + Solver!RC" & 31 + i & " * "Input!R" & 6 + i & "C[2]" code is generate the code ActiveCell.FormulaR1C1 = _
"=((Input!R[22]C[2]/Input!R24C[2]-(Solver!RC32*Input!R7C[2]+Solver!RC33*Input!R8C[2]+Solver!RC34*Input!R9C[2]+Solver!RC35*Input!R10C[2]+Solver!RC36*Input!R11C[2]))^2)" more generally for any values of M, i.e. for i varying.

I can write the above formula for each case of M, expanding it by the appropriate +Solver!RCx*Input!RyC[2] term and it would work (or has so far for M = 2 to 5 that I have done) but M can be anything from 2 to 15, so I am trying to make it neater by using the code I have shown in the box so I can avoid writing 15 cases. Trouble is it won't run OK as-is.

Pred = Pred + Solver!RC" & 31 + i & " * "Input!R" & 6 + i & "C[2]" may not be the only coding error of course in this segment of code. I'll have to deal with any other errors one by one as I correct lines of code.

Appreciate any extra advice.
DC

thanks - your tip for the first problem appears to have fixed the problem all through this part of the sub, just having problems with:

' hide unused columns
    i = 32 + M
    j = 49 + M
    k = 66 + M
    
    Range(R8Ci, R8C44).Select
    Selection.EntireColumn.Hidden = True
    Range(R8Cj, R8C63).Select
    Selection.EntireColumn.Hidden = True
    Range(R8Ck, R8C80).Select
    Selection.EntireColumn.Hidden = True

Originally Posted by DaveCott

thanks - your tip for the first problem appears to have fixed the problem all through this part of the sub, just having problems with:

' hide unused columns
    i = 32 + M
    j = 49 + M
    k = 66 + M
    
    Range(R8Ci, R8C44).Select
    Selection.EntireColumn.Hidden = True
    Range(R8Cj, R8C63).Select
    Selection.EntireColumn.Hidden = True
    Range(R8Ck, R8C80).Select
    Selection.EntireColumn.Hidden = True

down to solving why

Range("B9").Select
        
    Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
    
    If n = 4 Then
    
    Pred = 0

For i = 1 To M
j = 31 + i
k = 6 + i

Pred = Pred + Solver!RC" & j & "  * Input!R" & k & "C[2]

Next i
    
    For j = 1 To 13
    
    'change to 32
'    ActiveCell.FormulaR1C1 = _
        "=((Input!R[22]C[2]/Input!R24C[2]-(Solver!RC32*Input!R7C[2]+Solver!RC33*Input!R8C[2]+Solver!RC34*Input!R9C[2]+Solver!RC35*Input!R10C[2]+Solver!RC36*Input!R11C[2]))^2)"
     
 '   ActiveCell.Offset(0, j) = _
        "=((Input!R[22]C[2]/Input!R24C[2]-(Solver!RC32*Input!R7C[2]+Solver!RC33*Input!R8C[2]+Solver!RC34*Input!R9C[2]+Solver!RC35*Input!R10C[2]+Solver!RC36*Input!R11C[2]))^2)"
    
  '  Next j
    
    ActiveCell.FormulaR1C1 = _
        "=(1*(Input!R[22]C[2]/Input!R24C[2]-Pred))^2)"
    
    ActiveCell.Offset(0, j) = _
        "=(1*(Input!R[22]C[2]/Input!R24C[2]-Pred))^2)"
    
    Next j

does not work now.

Pred = Pred + Solver!RC" & j & "  * Input!R" & k & "C[2]

being the problem

Try

pred =pred + Worksheets("Solver").Cells(1, 1).Offset(0, j-1).Value * Worksheets("Input").Cells(1, 1).Offset(k-1, 1)

thanks, problems with this still
1) when changing to your suggested code I'm not sure how the C[2] at the end of the original code for the input sheet is reflected in your suggested code as it seems to be missing. Column seems to be 1, not 2 to the right, i.e. [2]?
2) my code first positions you at B9, so the row on the solver sheet for the first loop is row 9, does your code put it at row 9 or 1?
3) when I ran your code it then stopped at

ActiveCell.FormulaR1C1 = _
        "=(1*(Input!R[22]C[2]/Input!R24C[2]-Pred))^2)

, so something wrong with that next.
4) when I replaced the above code with ActiveCell.FormulaR1C1 = Pred to try to see what was happening these cell values were zero, which suggests the calculation of pred was not working yet.
..so back to the drawing board.

How is the weather in Hampshire anyway? Nice winter's day here.

Could you upload a copy of your spreadsheet after removing any sensitive information and with a small amount of data? It will make it much easier to see what's happenening. Just click Go Advanced and click on the paper clip.

By the way, it's a typical summer's day here - cool, wet, windy and grey.

Hi Huron
Back in my office after the weekend.
I've made a copy of the relevant part of the sheet with data for M=5. The sub CalcSS() in the solve module for the case 5 (ie M=5) works OK with the specific code for M=5.
I have commented out the more generic code I was trying to create, with your help, for M = anything from 2 to 15. teh geenric code does not currently work. What would be helpful is getting the generic code that uses pred to work. I would then replace all the specific code for cases 2 to 15 with this (i've coded cases 2-6 so far). You may need to add in solver.xla to project references to enable solver to run.
Look forward to seeing what you can come up with.

Hello marcol. I spent 3 months in Scotland last year and really enjoyed it.
Thanks for taking a look at this. From my quick run of your sheet it appears to work.
I will now apply your coding logic to the whole section including the different options in the larger workbook and see if it contimues to work. I expect it will but this will take me awhile.
If it does work I will then immediately hit solved and rate you and huron highly as you have saved me much web trawling as computing coding is not my area of expertise. Thanks again.

It worked. Thanks again. I hit the scales of you both.

Thanks Alistair. I can see that your suggestion is a bit neater for when n=4. The front weighting term just drops out then as it is =1. The weights I will actually use are not 1 but they were not needed to fix the code. I did see the formula sheets and your logic to create lead, body and tails of equations. My problem was that I am using a very dusty memory of basic/fortran over 30 years ago at Uni. with no VBA training, so I don't know the legal VBA syntax in some situations, ie. I'm fine with the logic but not always the syntax. This bit is actually only a small part of the workbook as the solver solutions have to be processed a lot further. Thanks again for your helpful look at this.

Our 3 months was spent at SAC, Penicuik, living in Gorebridge last year. We were there during the Edinburgh Festival which was great, as was the Glass House off the mile from memory.

PS, your neater coding worked fine.

Hey, if you remember the festival, you weren't there! ... ... ,,,

Na bi fada gun tilleadh (Haste ye back)

Slainte
Alistair

Remember bits of it! Spent more money on shows than grog! We hope to get back. Really enjoyed our travels up north&west of Aberdeen. Finished my Talisker whisky so will have to go back one day.

Try Lagavulin next time, you might just stay for ever!