cell references help needed

[DaveCott]

Hello
I am having a problem with the syntax of cell references in two places in an Xcel program.
Someone may be able to see the problem with the code very quickly.
Appreciate any code change suggestions.

1) I am trying to write general code that will handle any number of components (=M).

Can you see what is wrong with this line of code:

ActiveCell.Offset(0, 1 + i) = "=Input!R[i]C[-30 - i]"

as shown below?

All the ‘commented out code is what the for loops are meant to do and is code that works when I run it specifically for M = 5

Case 5
' headings
    Range("AF8").Select
         
    ActiveCell.FormulaR1C1 = "=Input!R[-1]C[-29]"
    ActiveCell.Offset(0, -1) = "Animal"
    ActiveCell.Offset(0, 1) = "=Input!RC[-30]"
    
    For i = 1 To M - 2
   ActiveCell.Offset(0, 1 + i) = "=Input!R[i]C[-30 - i]"  
Next i
     
    'ActiveCell.Offset(0, 3) = "=Input!R[2]C[-32]"
    'ActiveCell.Offset(0, 4) = "=Input!R[3]C[-33]"
    'ActiveCell.Offset(0, 5) = "=Input!R[4]C[-34]"
    
    ActiveCell.Offset(0, 15) = "Total"
    ActiveCell.Offset(0, 16) = "=RC[-17]"
    ActiveCell.Offset(0, 17) = "=RC[-17]"
    ActiveCell.Offset(0, 18) = "=RC[-17]"
    
    For i = 1 To M - 2
    ActiveCell.Offset(0, 18 + i) = "=RC[-17]"
    
    'ActiveCell.Offset(0, 20) = "=RC[-17]"
    'ActiveCell.Offset(0, 21) = "=RC[-17]"
    'ActiveCell.Offset(0, 22) = "=RC[-17]"
    Next i
    
    ActiveCell.Offset(0, 23) = "=RC[-17]"
    ActiveCell.Offset(0, 32) = "=RC[-17]"
    ActiveCell.Offset(0, 33) = "=RC[-17]"
    ActiveCell.Offset(0, 34) = "=RC[-17]"
    ActiveCell.Offset(0, 35) = "=RC[-17]"
    
    For i = 1 To M - 2
    ActiveCell.Offset(0, 35 + i) = "=RC[-17]"
    
    'ActiveCell.Offset(0, 37) = "=RC[-17]"
    'ActiveCell.Offset(0, 38) = "=RC[-17]"
    'ActiveCell.Offset(0, 39) = "=RC[-17]"
    Next i
           
    ActiveCell.Offset(0, 49) = "=RC[-17]"
    ActiveCell.Offset(0, 51) = "Digestibility"
    ActiveCell.Offset(0, 52) = "Faecal output (kg)"
        
    'values
    Range("BO9").Offset(0, M - 2).Select
        
    Do While IsEmpty(ActiveCell.Offset(0, -34 - M)) = False
    'CSM kg
        
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-34 - M],CSMlist,5)"
    'total
    ActiveCell.Offset(0, -18 - M) = "=SUM(RC[-15]:RC[-16 + M])"
   
   ' proportions
    ActiveCell.Offset(0, -17 - M) = "=RC[-17]"
    
    For i = 1 To M
    ActiveCell.Offset(0, -16 - i) = "=RC[-17]/RC47"
    Next i
    
    'ActiveCell.Offset(0, -21) = "=RC[-17]/RC47"
    'ActiveCell.Offset(0, -20) = "=RC[-17]/RC47"
         
    ActiveCell.Offset(0, -1 - M) = "=SUM(RC[-15]:RC[-1])"
    ActiveCell.Offset(0, -M) = "=RC[-17]"
    
    'kg
    For i = 1 To M - 1
    ActiveCell.Offset(0, i - M) = "=RC[-17]*RC[65+M]/RC[48+M]"
    Next i
   
    'ActiveCell.Offset(0, -4) = "=RC[-17]*RC71/RC54"
    'ActiveCell.Offset(0, -3) = "=RC[-17]*RC71/RC54"
    'ActiveCell.Offset(0, -2) = "=RC[-17]*RC71/RC54"
    'ActiveCell.Offset(0, -1) = "=RC[-17]*RC71/RC54"
    
    ActiveCell.Offset(0, 16 - M) = "=SUM(RC[-15]:RC[-1])"
    
        'digest
    ActiveCell.Offset(0, 18 - M) = "=(RC[-36]-1)/RC[-36]"
    ActiveCell.Offset(0, 19 - M) = "=RC[-3]*(1-RC[-1])"
    ActiveCell.Offset(1, 0).Select
    Loop
    
    
 ' hide unused columns
    Range(R8C32 + M, R8C44).Select
    Selection.EntireColumn.Hidden = True
    Range(R8C49 + M, R8C63).Select
    Selection.EntireColumn.Hidden = True
    Range(R8C66 + M, R8C80).Select
    Selection.EntireColumn.Hidden = True

2) I cannot get

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

to work below.

Again the ‘commented out code that it is designed to replace, works

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

For i = 1 To M

Pred = Pred + Solver!RC" & 31 + i & " * "Input!R" & 6 + i & "C[2]"
Next i
    
    For j = 1 To 13
    
'    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

I’ve tried different coding w/o success.

[DaveCott]

help still needed

[Huron]

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?

[DaveCott]

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

[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

[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

[Huron]

Try

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

[DaveCott]

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.

[Huron]

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.

[DaveCott]

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.

[Marcol]

Try this workbook to see if I'm on the right lines.

The code could still be improved, but let's see if it works first.

I'm not sure about the If statement in Sub CalcSS, the else option.
I suspect this statement could be redundant but would need to know the rules a bit better.

Maybe a sample where we could choose the options would be an advantage?

Hope this helps.

[DaveCott]

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.

[DaveCott]

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

[Marcol]

Bit of a lucky guess with the if statement!
I feel that you could just use the else option and this part (cell)

strLead = "=(R" & n + 3 & "C"

would return 1 when n =4

It could at least be simplified a little try this statement

    With wsSolver.Range("B9")
        ctrR1 = 33
        ctrR2 = 8

        For ctr = 1 To M - 1
            strBody = strBody & "+" & "Solver!RC" & ctrR1 & "*Input!R" & ctrR2 & "C[2]"
            ctrR1 = ctrR1 + 1
            ctrR2 = ctrR2 + 1
        Next
        strTail = "))^2)"

        If n = 4 Then
            strLead = "=((Input!R[22]C[2]/Input!R24C[2]-(Solver!RC32*Input!R7C[2]"
            strFormula = strLead & strBody & strTail
            .Resize(LastRow + 1 - FirstRow, 14).FormulaR1C1 = strFormula
            .Offset(0, 15).Resize(LastRow + 1 - FirstRow, 11).FormulaR1C1 = strFormula
        Else
            strLead = "=(R" & n + 3 & "C"
            strLead = strLead & "*(Input!R[22]C[2]/Input!R24C[2]-(Solver!RC32*Input!R7C[2]"
            strFormula = strLead & strBody & strTail
            .Resize(LastRow + 1 - FirstRow, 14).FormulaR1C1 = strFormula
            .Offset(0, 15).Resize(LastRow + 1 - FirstRow, 11).FormulaR1C1 = strFormula
        End If
        .Offset(0, 14).Resize(LastRow + 1 - FirstRow, 1).FormulaR1C1 = "=RC[-15]"
        .Offset(0, 26).Resize(LastRow + 1 - FirstRow, 1).FormulaR1C1 = "= SUM(RC[-26]:RC[-1])"
        .Offset(0, 29).Resize(LastRow + 1 - FirstRow, 1).FormulaR1C1 = "= RC[-30]"
    End With

As you will have noticed, I left the formula breakdown in the workbook to help explain the logic and build up. With long formula this is often a good way to tackle the problem in VBa.

Anyhow, it seems to fit the bill, and that's good.

Thanks for the rep and glad to hear you enjoyed your stay in our part of this little island, as we say "Na bi fada gun tilleadh" or in the English "Haste ye back."

Slainte
Alistair

[DaveCott]

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.

[DaveCott]

PS, your neater coding worked fine.

[Marcol]

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

Na bi fada gun tilleadh (Haste ye back)

Slainte
Alistair

[DaveCott]

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.

[Marcol]

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