JosephTL
New Member
 Joined
 Apr 13, 2020
 Messages
 5
 Office Version

 2016
 2013
 2011
 2010
 Platform

 Windows
Good day, 'TLDR below, this is just for context
This question does not necessarily need to be answered as long as there is a solution to my problem one way or another.
Background of what I'm attempting to accomplish.
I'm building a usage model where I take several hundred jobs (400600), compare their active dates Vs the dates throughout the year, and multiply the amount of equipment they use (~2600 unique pcs) vs 1 of 6 percent charts.
I already built this IN an excel formula which works pretty well... Issue is I had to break up each month to a single spreadsheet, the year file into another, and import the values torun my capital expense projections//usage shortages. Each month sheet(~3040mb) takes my computer 2 minutes to open and 2 minutes to save/close, so whenever I want to make an adjustment it takes about 30+minutes and a reimport. See formula below(or skip not that important)(it is just a nightmare that took me like a week to get right).
=IFERROR(ROUNDUP(IF(OR([@Category]="GRD",[@Category]="STR"),IF(OR(VLOOKUP(G$1 & "*",AllJobs,5,0)>Apr_1,VLOOKUP(G$1 & "*",AllJobs,9,0)<Apr_1),0,IF(AND(VLOOKUP(G$1 & "*",AllJobs,6,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,8,0)>Apr_1),AprProjection!G3,IF(AND(VLOOKUP(G$1 & "*",AllJobs,5,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,6,0)>=Apr_1),AprProjection!G3*@INDEX(Picket_Steps[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Picket_Steps_Strike[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,8,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,26,0))))),IF([@Category]="ACC",IF(OR(VLOOKUP(G$1 & "*",AllJobs,5,0)>Apr_1,VLOOKUP(G$1 & "*",AllJobs,9,0)<Apr_1),0,IF(AND(VLOOKUP(G$1 & "*",AllJobs,6,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,8,0)>Apr_1),AprProjection!G3,IF(AND(VLOOKUP(G$1 & "*",AllJobs,5,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,6,0)>=Apr_1),AprProjection!G3*@INDEX(Special_Items[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Special_Items_Strike[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,8,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,26,0))))),IF(OR(VLOOKUP(G$1 & "*",AllJobs,5,0)>Apr_1,VLOOKUP(G$1 & "*",AllJobs,9,0)<Apr_1),0,IF(AND(VLOOKUP(G$1 & "*",AllJobs,6,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,8,0)>Apr_1),AprProjection!G3,IF(AND(VLOOKUP(G$1 & "*",AllJobs,5,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,6,0)>=Apr_1),AprProjection!G3*@INDEX(Understructure[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Understructure_Strike[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,8,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,26,0))))))),0),AprProjection!G3)
So I would like to combine everything into 1 sheet and eliminate 90% of my formulas with a vba code which I can run on demand... I expect it to be pretty beefy and still take a few minutes to compile but I need to make it so i can make all the changes I need to then run the code on demand...(I'm also trying to be efficient considering the code will have to run something like 88mil? loops? maybe my math is bad, depends on how the output if accomplished)
My VBA is passable sometimes lol please don't judge since I'm working on framing work, I believe I know where I want to go so I'm working on each part individually until I get them working, once they work I just push them to the side until the next is done and so forth until I get everything done then I will combine them all and break it differently... I also use msg boxes to step through the code to see where it breaks...
What I'm stuck on currently is changing the value of an array. I have the array, I've set the value of the array to be that of the "TakeOff" (see below the sheet)(just a small sample)
The array is currently set up to be 1d(=solojob) and it would equal the entirety of COL L (in this case "American C")
For jobnum = 1 To JobNumbers
Set SoloJob = Sheets("Event_Page").Range("b2:ad2").Offset(jobnum, 0)
'if there is no t/o for the job in question it just skips to next job
If SoloJob(29) = 0 Then
GoTo Nextjobnum
End If
'populates t/o of job into an array
'Set SoloTO = Sheets("Total_TOs").Range("h3:h2619").Offset(0, SoloJob(29)) '''''(Both set soloto and soloto.value seem to work so i was experimenting with both)
SoloTO = Sheets("Total_TOs").Range("h3:h2619").Offset(0, SoloJob(29)).Value
Now I can output this information
'Sheets(SheetsForTables(0)).Range("g2:g2619").Offset(0, SoloJob(29)) = SoloTO() 'works just need to find a way to multiply
However I need to adjust the data under variable circumstance so I have 6 2d arrays I need to reference to multiply by the dependent on the the date (below is the for if loops in you care for a reference guide all that is there is the framework. Not only am I referencing dates but if the Category col (above) is "UND" I need to multiply by "x", if its "STR" I need to multiply by "y", and if its "ACC" I need to multiply by "z"
'converts date to double for math later changes for each job
StartBDateToDbl = SoloJob(5)
EndBDateToDbl = SoloJob(6)
StartSDateToDbl = SoloJob(8)
EndSDateToDbl = SoloJob(9)
'start to load in the t/o to tables where it pertains
For Week = 43831 To 44202 'need to set to variable for 18 month range
'check that the current "week" is within the job timeline
If Week >= StartBDateToDbl And Week <= EndSDateToDbl Then
'paste using indexed multiplied on tables by week
'week  43824 / 7 (gives me the week number)(x)
'x
'if event week 100%
'else build 100% * usagebuild
'else strike 100% * usagestrike
If Week > EndBDateToDbl And Week < StartSDateToDbl Then
'paste 100% t/o
GoTo NextWeek
Else
If Week <= EndBDateToDbl Then
'paste indexed number build 100% * usagebuild
GoTo NextWeek
Else
If Week >= StartSDateToDbl Then
100% * usagestrike
GoTo NextWeek
Else
MsgBox ("major error in nest of date display tos")
End If
End If
End If
Else
'drops out if we are past event strike date end for speed
If Week < EndDateToDbl Then
GoTo Nextjobnum
End If
End If
NextWeek:
Week = Week + 6 'to keep it moving forward in week intervals
Next Week

TL;DR
I need to either multiply a 1d array with another 1d array
Array1 (1,3,5,4,3,............) * Array2(x,y,x,z,y,.............)
= Array3(1x,3y,5x,4z,3y,......) OR display in COL L (1x,3y,5x,4z,3y........)
OR populate a 2d array at several different times
Array
[ Multiple, T/O, Adjusted
? , 1 , ?
? , 3 , ?
? , 5 , ?
? , 4 , ?
? , 3 , ?]
'Do some stuff in between not really important but there is a time difference between when i can load in this information since I cant get it until this point
[ Multiple, T/O, Adjusted
x , 1 , ?
y , 3 , ?
x , 5 , ?
z , 4 , ?
y , 3 , ?]
'do some other stuff(same as before) (OR)(option 1)
[ Multiple, T/O, Adjusted
x , 1 , 1x
y , 3 , 3y
x , 5 , 5x
z , 4 , 4z
y , 3 , 3y]
Display Array(adjusted)="1x,3y,5x,4z,3y........"
OR(option 2)
Display array(multiple) * array(T/O) = "1x,3y,5x,4z,3y........"
*Notes, this does not have to be dynamic, I know everytime my 2d array will be 2619 in length
*I always pasting this information into a col on the same x axis, the y axis is determined with an offset
*I'm looking for the most efficient possible way to do it b/c of the quantity of lines * the quantity of times it will cycle through
*Once the data has been adjusted for the sheet it is no longer needed for other calculations
I'm sorry this was a mile long post, I hope you can garner enough information about what I'm doing//what I need without my code making your eyes bleed haha
I'm willing to discuss on skype or share my file if its too ambiguous what I'm attempting to accomplish through this post.
Thanks
This question does not necessarily need to be answered as long as there is a solution to my problem one way or another.
Background of what I'm attempting to accomplish.
I'm building a usage model where I take several hundred jobs (400600), compare their active dates Vs the dates throughout the year, and multiply the amount of equipment they use (~2600 unique pcs) vs 1 of 6 percent charts.
I already built this IN an excel formula which works pretty well... Issue is I had to break up each month to a single spreadsheet, the year file into another, and import the values torun my capital expense projections//usage shortages. Each month sheet(~3040mb) takes my computer 2 minutes to open and 2 minutes to save/close, so whenever I want to make an adjustment it takes about 30+minutes and a reimport. See formula below(or skip not that important)(it is just a nightmare that took me like a week to get right).
=IFERROR(ROUNDUP(IF(OR([@Category]="GRD",[@Category]="STR"),IF(OR(VLOOKUP(G$1 & "*",AllJobs,5,0)>Apr_1,VLOOKUP(G$1 & "*",AllJobs,9,0)<Apr_1),0,IF(AND(VLOOKUP(G$1 & "*",AllJobs,6,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,8,0)>Apr_1),AprProjection!G3,IF(AND(VLOOKUP(G$1 & "*",AllJobs,5,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,6,0)>=Apr_1),AprProjection!G3*@INDEX(Picket_Steps[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Picket_Steps_Strike[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,8,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,26,0))))),IF([@Category]="ACC",IF(OR(VLOOKUP(G$1 & "*",AllJobs,5,0)>Apr_1,VLOOKUP(G$1 & "*",AllJobs,9,0)<Apr_1),0,IF(AND(VLOOKUP(G$1 & "*",AllJobs,6,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,8,0)>Apr_1),AprProjection!G3,IF(AND(VLOOKUP(G$1 & "*",AllJobs,5,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,6,0)>=Apr_1),AprProjection!G3*@INDEX(Special_Items[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Special_Items_Strike[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,8,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,26,0))))),IF(OR(VLOOKUP(G$1 & "*",AllJobs,5,0)>Apr_1,VLOOKUP(G$1 & "*",AllJobs,9,0)<Apr_1),0,IF(AND(VLOOKUP(G$1 & "*",AllJobs,6,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,8,0)>Apr_1),AprProjection!G3,IF(AND(VLOOKUP(G$1 & "*",AllJobs,5,0)<=Apr_1,VLOOKUP(G$1 & "*",AllJobs,6,0)>=Apr_1),AprProjection!G3*@INDEX(Understructure[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Understructure_Strike[[1]:[18]],ROUNDUP((Apr_1VLOOKUP(G$1 & "*",AllJobs,8,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,26,0))))))),0),AprProjection!G3)
So I would like to combine everything into 1 sheet and eliminate 90% of my formulas with a vba code which I can run on demand... I expect it to be pretty beefy and still take a few minutes to compile but I need to make it so i can make all the changes I need to then run the code on demand...(I'm also trying to be efficient considering the code will have to run something like 88mil? loops? maybe my math is bad, depends on how the output if accomplished)
My VBA is passable sometimes lol please don't judge since I'm working on framing work, I believe I know where I want to go so I'm working on each part individually until I get them working, once they work I just push them to the side until the next is done and so forth until I get everything done then I will combine them all and break it differently... I also use msg boxes to step through the code to see where it breaks...
What I'm stuck on currently is changing the value of an array. I have the array, I've set the value of the array to be that of the "TakeOff" (see below the sheet)(just a small sample)
Category  Class  Part #  Sku  Description  Inventory  500 Festiv  Above the  Albertsons  American B  American C 
UND  UAE  BP1  10030001  Base Plate  Standard  61  53  0  34  0  51 
UND  UAE  BPG1SQP  10030004  Base Plate with Square Pin Galvanized  12  0  0  30  0  24 
UND  UAE  SJ15  10030005  Base Jack  15" (Short Jack)  309  0  0  331  0  184 
UND  UAE  SJ23  10030006  Base Jack  Regular 23"  538  0  0  383  0  718 
UND  UAE  WDF22  10030010  2' x 2' wide Walk Deck Frame  12  0  0  0  0  0 
UND  UAE  WDF22B  10030011  2' x 2' wide Walk Deck Frame  BLACK  0  0  0  0  0  0 
UND  UAE  WDF35  10030019  3' x 5' wide Walk Deck Frame  5  0  0  45  0  0 
UND  UAE  JR1  10030028  1' Leg Extension  32  0  0  39  0  30 
UND  UAE  BOXF1  10030038  1' x 6' Support Box Frame  233  0  0  33  0  38 
UND  UAE  BOXF14  10030042  1'4" x 6' Support Box Frame  10  0  0  33  0  38 
STR  UAE  BOXF2  10030043  2' x 6' Support Box Frame  74  0  0  114  0  181 
ACC  UAE  BOXF3  10030046  3' x 6' Support Box Frame  50  0  0  58  0  180 
UND  UAE  BOXF4  10030050  4' x 6' Support Box Frame  329  0  0  42  0  279 
UND  SBX  CR871  10030102  Chair Riser  7'  #1  8" Rise  9  0  0  0  0  10 
STR  SBX  CR881  10030103  Chair Riser  8'  #1  8" Rise  69  0  0  0  0  30 
STR  SBX  CR882  10030104  Chair Riser  8'  #2  8" Rise  20  0  0  0  0  30 
UND  SBX  CR1271  10030114  Chair Riser  7'  #1  12" Rise  36  0  0  0  0  0 
ACC  SBX  CR1281  10030116  Chair Riser  8'  #1  12" Rise  0  0  0  0  0  0 
ACC  SBX  CR1282  10030117  Chair Riser  8'  #2  12" Rise  17  0  0  0  0  0 
ACC  SBX  PBL12BTR  10030138  12' Bleacher Tunnel Truss Putlog  11  0  0  0  0  0 
ACC  SBX  PBL18BTR  10030140  18' Bleacher Tunnel Truss Putlog  3  0  0  0  0  0 
ACC  SBX  PBL86  10030147  8" x 6' Putlog  Type BL  53  0  0  26  0  56 
STR  SBX  PBL89  10030149  8" x 9' Putlog  Type BL  49  0  0  28  0  11 
UND  SBX  PBL812  10030150  8" x 12' Putlog  Type BL  51  0  0  28  0  19 
UND  SBX  PBL816  10030153  8" x 16' Putlog  Type BL  46  0  0  20  0  38 
UND  SYS  SYSVS1R  10030235  Vertical Starter  1 Ring  0  0  0  176  0  226 
UND  SYS  SYSV.51R  10030237  Vertical  1/2m  1 Ring  215  0  0  50  0  226 
UND  SYS  SYSV12R  10030238  Vertical  1m  2 Ring  215  0  0  41  0  274 
The array is currently set up to be 1d(=solojob) and it would equal the entirety of COL L (in this case "American C")
For jobnum = 1 To JobNumbers
Set SoloJob = Sheets("Event_Page").Range("b2:ad2").Offset(jobnum, 0)
'if there is no t/o for the job in question it just skips to next job
If SoloJob(29) = 0 Then
GoTo Nextjobnum
End If
'populates t/o of job into an array
'Set SoloTO = Sheets("Total_TOs").Range("h3:h2619").Offset(0, SoloJob(29)) '''''(Both set soloto and soloto.value seem to work so i was experimenting with both)
SoloTO = Sheets("Total_TOs").Range("h3:h2619").Offset(0, SoloJob(29)).Value
Now I can output this information
'Sheets(SheetsForTables(0)).Range("g2:g2619").Offset(0, SoloJob(29)) = SoloTO() 'works just need to find a way to multiply
However I need to adjust the data under variable circumstance so I have 6 2d arrays I need to reference to multiply by the dependent on the the date (below is the for if loops in you care for a reference guide all that is there is the framework. Not only am I referencing dates but if the Category col (above) is "UND" I need to multiply by "x", if its "STR" I need to multiply by "y", and if its "ACC" I need to multiply by "z"
'converts date to double for math later changes for each job
StartBDateToDbl = SoloJob(5)
EndBDateToDbl = SoloJob(6)
StartSDateToDbl = SoloJob(8)
EndSDateToDbl = SoloJob(9)
'start to load in the t/o to tables where it pertains
For Week = 43831 To 44202 'need to set to variable for 18 month range
'check that the current "week" is within the job timeline
If Week >= StartBDateToDbl And Week <= EndSDateToDbl Then
'paste using indexed multiplied on tables by week
'week  43824 / 7 (gives me the week number)(x)
'x
'if event week 100%
'else build 100% * usagebuild
'else strike 100% * usagestrike
If Week > EndBDateToDbl And Week < StartSDateToDbl Then
'paste 100% t/o
GoTo NextWeek
Else
If Week <= EndBDateToDbl Then
'paste indexed number build 100% * usagebuild
GoTo NextWeek
Else
If Week >= StartSDateToDbl Then
100% * usagestrike
GoTo NextWeek
Else
MsgBox ("major error in nest of date display tos")
End If
End If
End If
Else
'drops out if we are past event strike date end for speed
If Week < EndDateToDbl Then
GoTo Nextjobnum
End If
End If
NextWeek:
Week = Week + 6 'to keep it moving forward in week intervals
Next Week

TL;DR
I need to either multiply a 1d array with another 1d array
Array1 (1,3,5,4,3,............) * Array2(x,y,x,z,y,.............)
= Array3(1x,3y,5x,4z,3y,......) OR display in COL L (1x,3y,5x,4z,3y........)
OR populate a 2d array at several different times
Array
[ Multiple, T/O, Adjusted
? , 1 , ?
? , 3 , ?
? , 5 , ?
? , 4 , ?
? , 3 , ?]
'Do some stuff in between not really important but there is a time difference between when i can load in this information since I cant get it until this point
[ Multiple, T/O, Adjusted
x , 1 , ?
y , 3 , ?
x , 5 , ?
z , 4 , ?
y , 3 , ?]
'do some other stuff(same as before) (OR)(option 1)
[ Multiple, T/O, Adjusted
x , 1 , 1x
y , 3 , 3y
x , 5 , 5x
z , 4 , 4z
y , 3 , 3y]
Display Array(adjusted)="1x,3y,5x,4z,3y........"
OR(option 2)
Display array(multiple) * array(T/O) = "1x,3y,5x,4z,3y........"
*Notes, this does not have to be dynamic, I know everytime my 2d array will be 2619 in length
*I always pasting this information into a col on the same x axis, the y axis is determined with an offset
*I'm looking for the most efficient possible way to do it b/c of the quantity of lines * the quantity of times it will cycle through
*Once the data has been adjusted for the sheet it is no longer needed for other calculations
I'm sorry this was a mile long post, I hope you can garner enough information about what I'm doing//what I need without my code making your eyes bleed haha
I'm willing to discuss on skype or share my file if its too ambiguous what I'm attempting to accomplish through this post.
Thanks