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>

 

[Norie]

Alan

You can't refer to variables like that.

Why not use arrays?

If they need to change size depending on the count then you can use ReDim.

 

[AlanAnderson]

Hi Norie,

I posted a query earlier on the issue of arrays. I have never used them and what I've read just confuses and scares me.

If you dont mind, I'll try bump my array question to the top. I'd really appreciate it if you could answer that. It might solve this question as well.

Regards,

Alan

 

[AlanAnderson]

Hi Norie,
Would you please be so kind as to look at my other question "Using Arrays instead of this code"

Thanks

Alan

 

[Norie]

Alan

Can you post a link to the other thread?

Otherwise I'll never find it.

 

[AlanAnderson]

Hi Norie,

Here is the link. If yoyu have a problem a search with the keyword arrays does find it.

Thanks

Alan

http://www.mrexcel.com/forum/showthread.php?t=540450&goto=newpost

 

[Norie]

Alan

Weren't you given a solution, or some ideas for one, in that thread?

Perhaps it wasn't exactly what you needed but you seemed rather reluctant to give a full explanation of what data you were dealing with and what you were trying to do.

In that thread you mention the code has become too large to compile?

Why not post some of the problem code?

Not all of it obviously.

Seeing some of it might help us give you some pointers with it.

Often when code gets so big there are simple changes that can be made, and those are usually applicable throughout the code.

PS Some sample of the 'real' data would be helpful too.

 

[AlanAnderson]

Hi Norie,
The following is another message I posted. The code here is really the issue.
Not only do I HAVE to reduce the size, but it is also slow.

"I have a procedure that basically consists of the following code repeated 34 times (line numbers change EG the next time the +6 is replaced by +7 and so on). As there is little else in the procedure I have no logical way of splitting the code into separate procedures.

Can anyone suggest how I can take my clumsy code and reduce it in size so I no longer get the Procedure Too Large error?



Code:

wsInv.Cells(WriteRowInv + 6, 1) = wsIQ.Cells(1, 24) wsInv.Cells(WriteRowInv + 6, 2) = wsIQ.Cells(i, 24) wsInv.Cells(WriteRowInv + 6, 3) = wsIQ.Cells(i, 25) wsInv.Cells(WriteRowInv + 6, 4) = wsIQ.Cells(i, 26) wsInv.Cells(WriteRowInv + 6, 5) = Val(wsInv.Cells(WriteRowInv + 6, 2) / 100 * vTotal) wsInv.Cells(WriteRowInv + 6, 6) = Val(wsInv.Cells(WriteRowInv + 6, 5) * vFxBase) If wsInv.Cells(WriteRowInv + 6, 4) = True Then wsInv.Cells(WriteRowInv + 6, 7) = Val(wsInv.Cells(WriteRowInv + 6, 5) * vFXNow) Else wsInv.Cells(WriteRowInv + 6, 7) = wsInv.Cells(WriteRowInv + 6, 6) End If If wsInv.Cells(WriteRowInv + 6, 3) = True Then wsInv.Cells(WriteRowInv + 6, 8) = Val(wsInv.Cells(WriteRowInv + 6, 7) / vIndexBase * vIndexNow) ' NEED TO GET BOTH VARS Else wsInv.Cells(WriteRowInv + 6, 8) = wsInv.Cells(WriteRowInv + 6, 7) End If 'This checks if item taxable For u = 2 To FinalRowVar vComp = wsInv.Cells(WriteRowInv + 6, 1) If vComp = Sheets("Variables").Cells(u, 12) Then wsInv.Cells(WriteRowInv + 6, 9) = Sheets("Variables").Cells(u, 13) Next u 'If Yes looks up Vat rate at calcs <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:City w:st="on">Vat</st1:City> <st1:State w:st="on">Col</st1:State></st1lace> (10) and new total (11) If wsInv.Cells(WriteRowInv + 6, 9) = "Y" Then wsInv.Cells(WriteRowInv + 6, 10) = wsInv.Cells(WriteRowInv + 6, 8) * Sheets("Variables").Cells(2, 4) / 100 wsInv.Cells(WriteRowInv + 6, 11) = wsInv.Cells(WriteRowInv + 6, 8) + wsInv.Cells(WriteRowInv + 6, 10) Else wsInv.Cells(WriteRowInv + 6, 10) = 0 wsInv.Cells(WriteRowInv + 6, 11) = wsInv.Cells(WriteRowInv + 6, 8) End If FinalRowVar = Sheets("Variables").Cells(Rows.Count, 11).End(xlUp).Row For g = 2 To FinalRowVar If wsIQ.Cells(1, 27) = Sheets("Variables").Cells(g, 11) Then wsInv.Cells(WriteRowInv + 7, 1) = Sheets("Variables").Cells(g, 12) End If Next g</PRE>
Regards

Alan
<!-- / message -->

 

[AlanAnderson]

Hi Sorry, my cut and paste did not work - lets try again

[FONT=Times New Roman][SIZE=3]wsInv.Cells(WriteRowInv + 6, 1) = wsIQ.Cells(1, 24)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]      wsInv.Cells(WriteRowInv + 6, 2) = wsIQ.Cells(i, 24)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      wsInv.Cells(WriteRowInv + 6, 3) = wsIQ.Cells(i, 25)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      wsInv.Cells(WriteRowInv + 6, 4) = wsIQ.Cells(i, 26)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      wsInv.Cells(WriteRowInv + 6, 5) = Val(wsInv.Cells(WriteRowInv + 6, 2) / 100 * vTotal)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      wsInv.Cells(WriteRowInv + 6, 6) = Val(wsInv.Cells(WriteRowInv + 6, 5) * vFxBase)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          If wsInv.Cells(WriteRowInv + 6, 4) = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 7) = Val(wsInv.Cells(WriteRowInv + 6, 5) * vFXNow)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 7) = wsInv.Cells(WriteRowInv + 6, 6)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          If wsInv.Cells(WriteRowInv + 6, 3) = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 8) = Val(wsInv.Cells(WriteRowInv + 6, 7) / vIndexBase * vIndexNow) ' NEED TO GET BOTH VARS[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 8) = wsInv.Cells(WriteRowInv + 6, 7)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          'This checks if item taxable[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          For u = 2 To FinalRowVar[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              vComp = wsInv.Cells(WriteRowInv + 6, 1)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                  If vComp = Sheets("Variables").Cells(u, 12) Then wsInv.Cells(WriteRowInv + 6, 9) = Sheets("Variables").Cells(u, 13)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Next u[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          'If Yes looks up Vat rate at calcs <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:City w:st="on">Vat</st1:City> <st1:State w:st="on">Col</st1:State></st1:place> (10) and new total (11)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          If wsInv.Cells(WriteRowInv + 6, 9) = "Y" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 10) = wsInv.Cells(WriteRowInv + 6, 8) * Sheets("Variables").Cells(2, 4) / 100[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 11) = wsInv.Cells(WriteRowInv + 6, 8) + wsInv.Cells(WriteRowInv + 6, 10)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 10) = 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              wsInv.Cells(WriteRowInv + 6, 11) = wsInv.Cells(WriteRowInv + 6, 8)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          FinalRowVar = Sheets("Variables").Cells(Rows.Count, 11).End(xlUp).Row[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          For g = 2 To FinalRowVar[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              If wsIQ.Cells(1, 27) = Sheets("Variables").Cells(g, 11) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                    wsInv.Cells(WriteRowInv + 7, 1) = Sheets("Variables").Cells(g, 12)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Next g[/FONT][/SIZE]

 

[Norie]

Alan

I don't have time to have a proper look at the code - I didn't actually expect you to repost it so soon.

Anyway, from a quick look, the first thing I've got to ask is why you seem to be doing all these calculations in the code?

Why don't you do them with Excel formulas which you can enter via the code?

Even if you are calculating some variables used in the calculations in the code why not store either the results of the calculations somewhere on a worksheet?

You might even be able to do the calculations using worksheet formulas rather than code.

Oh, one thing I almost forgot - are you replicating this code a lot, perhaps with only slight changes?