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>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 7, 2010
Messages
134
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

Board Regular
Joined
Jun 7, 2010
Messages
134
Hi Norie,
Would you please be so kind as to look at my other question "Using Arrays instead of this code"

Thanks

Alan
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Alan

Can you post a link to the other thread?

Otherwise I'll never find it.:)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 7, 2010
Messages
134
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 -->
 

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
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]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,477
Members
409,702
Latest member
thmoriarty

This Week's Hot Topics

Top