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 (400-600), 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(~30-40mb) 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 re-import. 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_1-VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Picket_Steps_Strike[[1]:[18]],ROUNDUP((Apr_1-VLOOKUP(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_1-VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Special_Items_Strike[[1]:[18]],ROUNDUP((Apr_1-VLOOKUP(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_1-VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Understructure_Strike[[1]:[18]],ROUNDUP((Apr_1-VLOOKUP(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 "Take-Off" (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 (400-600), 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(~30-40mb) 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 re-import. 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_1-VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Picket_Steps_Strike[[1]:[18]],ROUNDUP((Apr_1-VLOOKUP(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_1-VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Special_Items_Strike[[1]:[18]],ROUNDUP((Apr_1-VLOOKUP(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_1-VLOOKUP(G$1 & "*",AllJobs,5,0))/7,0),VLOOKUP(G$1 & "*",AllJobs,24,0)),AprProjection!G3*@INDEX(Understructure_Strike[[1]:[18]],ROUNDUP((Apr_1-VLOOKUP(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 "Take-Off" (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 | BP-1 | 10030001 | Base Plate - Standard | -61 | 53 | 0 | 34 | 0 | 51 |
| UND | UAE | BPG-1-SQP | 10030004 | Base Plate with Square Pin Galvanized | -12 | 0 | 0 | 30 | 0 | 24 |
| UND | UAE | SJ-15 | 10030005 | Base Jack - 15"- (Short Jack) | -309 | 0 | 0 | 331 | 0 | 184 |
| UND | UAE | SJ-23 | 10030006 | Base Jack - Regular 23" | 538 | 0 | 0 | 383 | 0 | 718 |
| UND | UAE | WDF-22 | 10030010 | 2' x 2' wide Walk Deck Frame | 12 | 0 | 0 | 0 | 0 | 0 |
| UND | UAE | WDF-22-B | 10030011 | 2' x 2' wide Walk Deck Frame - BLACK | 0 | 0 | 0 | 0 | 0 | 0 |
| UND | UAE | WDF-35 | 10030019 | 3' x 5' wide Walk Deck Frame | 5 | 0 | 0 | 45 | 0 | 0 |
| UND | UAE | JR-1 | 10030028 | 1' Leg Extension | -32 | 0 | 0 | 39 | 0 | 30 |
| UND | UAE | BOXF-1 | 10030038 | 1' x 6' Support Box Frame | -233 | 0 | 0 | 33 | 0 | 38 |
| UND | UAE | BOXF-1-4 | 10030042 | 1'-4" x 6' Support Box Frame | -10 | 0 | 0 | 33 | 0 | 38 |
| STR | UAE | BOXF-2 | 10030043 | 2' x 6' Support Box Frame | -74 | 0 | 0 | 114 | 0 | 181 |
| ACC | UAE | BOXF-3 | 10030046 | 3' x 6' Support Box Frame | 50 | 0 | 0 | 58 | 0 | 180 |
| UND | UAE | BOXF-4 | 10030050 | 4' x 6' Support Box Frame | 329 | 0 | 0 | 42 | 0 | 279 |
| UND | SBX | CR-8-7-1 | 10030102 | Chair Riser - 7' - #1 - 8" Rise | -9 | 0 | 0 | 0 | 0 | 10 |
| STR | SBX | CR-8-8-1 | 10030103 | Chair Riser - 8' - #1 - 8" Rise | 69 | 0 | 0 | 0 | 0 | 30 |
| STR | SBX | CR-8-8-2 | 10030104 | Chair Riser - 8' - #2 - 8" Rise | 20 | 0 | 0 | 0 | 0 | 30 |
| UND | SBX | CR-12-7-1 | 10030114 | Chair Riser - 7' - #1 - 12" Rise | 36 | 0 | 0 | 0 | 0 | 0 |
| ACC | SBX | CR-12-8-1 | 10030116 | Chair Riser - 8' - #1 - 12" Rise | 0 | 0 | 0 | 0 | 0 | 0 |
| ACC | SBX | CR-12-8-2 | 10030117 | Chair Riser - 8' - #2 - 12" Rise | -17 | 0 | 0 | 0 | 0 | 0 |
| ACC | SBX | P-BL-12BTR | 10030138 | 12' Bleacher Tunnel Truss Putlog | 11 | 0 | 0 | 0 | 0 | 0 |
| ACC | SBX | P-BL-18BTR | 10030140 | 18' Bleacher Tunnel Truss Putlog | 3 | 0 | 0 | 0 | 0 | 0 |
| ACC | SBX | P-BL8-6 | 10030147 | 8" x 6' Putlog - Type BL | 53 | 0 | 0 | 26 | 0 | 56 |
| STR | SBX | P-BL8-9 | 10030149 | 8" x 9' Putlog - Type BL | 49 | 0 | 0 | 28 | 0 | 11 |
| UND | SBX | P-BL8-12 | 10030150 | 8" x 12' Putlog - Type BL | 51 | 0 | 0 | 28 | 0 | 19 |
| UND | SBX | P-BL8-16 | 10030153 | 8" x 16' Putlog - Type BL | 46 | 0 | 0 | 20 | 0 | 38 |
| UND | SYS | SYS-VS-1R | 10030235 | Vertical Starter - 1 Ring | 0 | 0 | 0 | 176 | 0 | 226 |
| UND | SYS | SYS-V.5-1R | 10030237 | Vertical - 1/2m - 1 Ring | -215 | 0 | 0 | 50 | 0 | 226 |
| UND | SYS | SYS-V1-2R | 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
