ItalianPlatinum
Well-known Member
- Joined
- Mar 23, 2017
- Messages
- 793
- Office Version
- 365
- 2019
- Platform
- Windows
Little background I have an inventory management tool that consolidates inventory for products. There is no reporting for this tool. So before I run it I have no idea how long or how many lots could be eliminated. Therefore I need visibility before executing. My journey has been to find a way to replicate what it does via a macro (VBA) as reporting. The tool combines lots if they match criteria (cost, identifier). I.e. if two occurrences for the same identifier it sells 2 and buys on a combined 1. Trying to capture that.
I am able to extract data from the system but that data is so large it reaches the excel row limit so I have to move the data into sheets to break up the data, for example if identifier starts with G I move it to Sheet G, and so forth. My VBA currently loops through each identifier (extracting from the system). If starts with A extract from system, then transfer to sheet A, then run for B, transfer to sheet B. I then apply the countIF to find out how many lots fit the parameters and that is where my problem resides. My battle is how to handle the data. I created a summary sheet that lists all identifiers but then I had problems getting that to work. I have 3,700 identifiers, among those there are a total of 3 million lots. When I ran the VBA it took at day to run
. Looking for some big time help here and willing to scrape it all if there is a better approach like leveraging the identifier approach on my summary sheet?
I am able to extract data from the system but that data is so large it reaches the excel row limit so I have to move the data into sheets to break up the data, for example if identifier starts with G I move it to Sheet G, and so forth. My VBA currently loops through each identifier (extracting from the system). If starts with A extract from system, then transfer to sheet A, then run for B, transfer to sheet B. I then apply the countIF to find out how many lots fit the parameters and that is where my problem resides. My battle is how to handle the data. I created a summary sheet that lists all identifiers but then I had problems getting that to work. I have 3,700 identifiers, among those there are a total of 3 million lots. When I ran the VBA it took at day to run
. Looking for some big time help here and willing to scrape it all if there is a better approach like leveraging the identifier approach on my summary sheet?
VBA Code:
With Worksheets(SheetName)
.Range("A1").Resize(lastRow - 10 + 1, 12).Value = WsSec.Range("A10:L" & lastRow).Value
lr1 = .Cells(rows.count, "A").End(xlUp).row
.Range("M1:Q1") = Array("Rounded 2 digit", "Lot Greater than 1yr", "Lot Greater than 3yr", "For formula", "Same Occurrence")
.Application.Calculation = xlAutomatic
.Range("P2:P" & lr1).NumberFormat = "General"
.Range("M2:M" & lr1).Formula = "=Round(K2, 2)"
.Range("N2:N" & lr1).Formula = "=if(RUN-E2>365,""YES"",""NO"")"
.Range("O2:O" & lr1).Formula = "=if(RUN-E2>(365*3),""YES"",""NO"")"
If TFLG = "N" Then
.Range("P2:P" & lr1).Formula = "=D2&M2"
Else
.Range("P2:P" & lr1).Formula = "=D2&E2&M2"
End If
.Range("P2:P" & lr1).NumberFormat = "@"
.Range("Q2").Formula2 = "=COUNTIF($P$2:$P$" & lr1 & ",$P$2:$P$" & lr1 & ")"
.Range("M2:P" & lr1).Value = .Range("M2:P" & lr1).Value
End With
VBA Code:
.Range("Q2").Formula2 = "=COUNTIF($P$2:$P$" & lr1 & ",$P$2:$P$" & lr1 & ")"
