Create Variables from other variables

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
This may seem silly but I have a blank regarding the logic.<o:p></o:p>
<o:p></o:p>
I need to set up a number of routines that will add totals.<o:p></o:p>
<o:p></o:p>
The actual number can vary. I can establish the number by doing a count (vCount)<o:p></o:p>
<o:p></o:p>
What I need to do, therefore, is have routines that basically say:<o:p></o:p>
Code:
[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:p></o:p>
and so on up to figure in vCount<o:p></o:p>
<o:p></o:p>
I imagine I’ll have a loop to the effect of:<o:p></o:p>
<o:p>
Code:
</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:p></o:p>
<o:p></o:p>
Then same routine needs to run again for vTotal & xx +1 and so on<o:p></o:p>
<o:p></o:p>



The questions:<o:p></o:p>
  • Am I am the right track?<o:p></o:p>
  • How do I establish xx in the above example<o:p></o:p>
<o:p></o:p>
Regards,<o:p></o:p>
<o:p></o:p>
Alan<o:p></o:p>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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
 
Upvote 0
Hi Norie,
Would you please be so kind as to look at my other question "Using Arrays instead of this code"

Thanks

Alan
 
Upvote 0
Alan

Can you post a link to the other thread?

Otherwise I'll never find it.:)
 
Upvote 0
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.
 
Upvote 0
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-com:eek:ffice: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) 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 -->
 
Upvote 0
Hi Sorry, my cut and paste did not work - lets try again


Code:
[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]
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top