Create Variables from other variables

[AlanAnderson]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This may seem silly but I have a blank regarding the logic.<o></o>
<o></o>
I need to set up a number of routines that will add totals.<o></o>
<o></o>
The actual number can vary. I can establish the number by doing a count (vCount)<o></o>
<o></o>
What I need to do, therefore, is have routines that basically say:<o></o>

[FONT=Times New Roman][SIZE=3]vStd1 + vDup1 + vTax1 = vTotal1 [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]vStd2 + vDup2 + vTax2 = vTotal2 [/FONT][/SIZE]

<o></o>
and so on up to figure in vCount<o></o>
<o></o>
I imagine I’ll have a loop to the effect of:<o></o>
<o>

</o:p>
[SIZE=3][FONT=Times New Roman]For i = vStart & xx to vEnd & xx<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]vTotal & xx = (i,vStd) + (i,vDup) + (i,vTax) ‘ Would be fields on Row i<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Next i  [/FONT][/SIZE]

<o></o>
<o></o>
Then same routine needs to run again for vTotal & xx +1 and so on<o></o>
<o></o>



The questions:<o></o>

• Am I am the right track?<o></o>
• How do I establish xx in the above example<o></o>

<o></o>
Regards,<o></o>
<o></o>
Alan<o></o>

 

[AlanAnderson]

Hi Norie,
1. The reason why calculations are in code rather than in sheets is that I intend to distribute this code to people a long way from me. That means updates of software become very complex if code/calculations are on the sheets. It is a worthwhile idea though. Maybe I can set up some "temporary calculation pages" that never retain permanent data.

Your second question is the heart of the problem. This code is replicated 34 times with minor changes. That's why I am desperate to either reduce its size or replace it with something better - I suspect that using an array might solve the size problem and speed up execution.
The changes for the 34 iterations are mainly the writerow and then the wsIQ cells in the first 4 lines. Example if you look at the first six lines of procedure abovethe next iteration would start as follows:

[FONT=Times New Roman][SIZE=3]wsInv.Cells(WriteRowInv + 7, 1) = wsIQ.Cells(1, 27)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 7, 2) = wsIQ.Cells(i, 27)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 7, 3) = wsIQ.Cells(i, 28)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 7, 4) = wsIQ.Cells(i, 29)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 7, 5) = Val(wsInv.Cells(WriteRowInv + 7, 2) / 100 * vTotal)[/FONT][/SIZE]

and the next:

[FONT=Times New Roman][SIZE=3]wsInv.Cells(WriteRowInv + 8, 1) = wsIQ.Cells(1, 30)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 8, 2) = wsIQ.Cells(i, 30)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 8, 3) = wsIQ.Cells(i, 31)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 8, 4) = wsIQ.Cells(i, 32)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wsInv.Cells(WriteRowInv + 8, 5) = Val(wsInv.Cells(WriteRowInv + 8, 2) / 100 * vTotal)[/FONT][/SIZE]

and so on.

Thanks again for your time and trouble.

Regards,

Alan