Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Visit Stack Exchange
Stack Internal
Knowledge at work
Bring the best of human thought and AI automation together at your work.
Explore Stack InternalSo thisThis is code iI wrote to check Producta product structure from 1 table in a database......so..... yea, why not right?
so itsIt's based on a type which can have area(s) and area(s) can have group(s).
so I have:
type and code area: blank group: blank type and area and code group:blank type and area and group and code so asAs you can see, it is stacked and the lookup is layered to find the correct code...Is Is there somethingssomething I can do to improve it?
So this is code i wrote to check Product structure from 1 table in a database......so..... yea, why not right?
so its based on type which can have area(s) and area(s) can have group(s)
so I have
type and code area: blank group: blank type and area and code group:blank type and area and group and code so as you can see it is stacked and the lookup is layered to find the correct code...Is there somethings I can do to improve it?
This is code I wrote to check a product structure from 1 table in a database. It's based on a type which can have area(s) and area(s) can have group(s).
I have:
type and code area: blank group: blank type and area and code group:blank type and area and group and code As you can see, it is stacked and the lookup is layered to find the correct code. Is there something I can do to improve it?
So this is code i wrote to check Product structure from 1 table in a database......so..... yea, why not right?
so its based on type which can have area(s) and area(s) can have group(s)
so I have
type and code area: blank group: blank type and area and code group:blank type and area and group and code so as you can see it is stacked and the lookup is layered to find the correct code...Is there somethings I can do to improve it?
Public Function StackLayeredLookup() Dim Start As Integer Dim lStart As Integer Dim cRows As Integer Dim lRows As Integer Dim TypeValue As String Dim TypeCode As String Dim AreaValue As String Dim AreaCode As String Dim GroupValue As String Dim GroupCode As String Dim aValue As String Dim dValue As String Const TypeCol = "G" Const AreaCol = "H" Const GroupCol = "I" Const RegCol = "E" Const tValueCol = "P" Const aValueCol = "Q" Const gValueCol = "R" 'Region = "MEX" Start = 2 cRows = Worksheets(2).UsedRange.Rows.Count lRows = Worksheets("TagCodes").UsedRange.Rows.Count Do Until Start = cRows TypeValue = Worksheets(2).Range(TypeCol & CStr(Start)).Value AreaValue = Worksheets(2).Range(AreaCol & CStr(Start)).Value GroupValue = Worksheets(2).Range(GroupCol & CStr(Start)).Value If (TypeValue <> "") Then TypeCode = "" lStart = 1 Do Until TypeCode <> "" Or lStart = lRows + 1 If (Worksheets("TagCodes").Range("A" & CStr(lStart)).Value = TypeValue) Then aValue = Worksheets("TagCodes").Range("C" & CStr(lStart)).Value gValue = Worksheets("TagCodes").Range("D" & CStr(lStart)).Value If (aValue = " " And gValue = " ") Then Worksheets(2).Range(tValueCol & CStr(Start)).Value = Worksheets("TagCodes").Range("B" & CStr(lStart)).Value TypeCode = Worksheets(2).Range(tValueCol & CStr(Start)).Value Exit Do End If Else lStart = lStart + 1 End If Loop If (TypeCode = "") Then Worksheets(2).Range(TypeCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) Else Worksheets(2).Range(TypeCol & CStr(Start)).Interior.Color = RGB(0, 255, 0) End If If (TypeCode <> "" And AreaValue <> "") Then AreaCode = "" lStart = 1 Do Until AreaCode <> "" Or lStart = lRows + 1 If (Worksheets("TagCodes").Range("A" & CStr(lStart)).Value = AreaValue) Then gValue = Worksheets("TagCodes").Range("D" & CStr(lStart)).Value If (Worksheets("TagCodes").Range("B" & CStr(lStart)).Value = TypeCode And gValue = " ") Then Worksheets(2).Range(aValueCol & CStr(Start)).Value = Worksheets("TagCodes").Range("C" & CStr(lStart)).Value AreaCode = Worksheets(2).Range(aValueCol & CStr(Start)).Value Exit Do Else lStart = lStart + 1 End If Else lStart = lStart + 1 End If Loop If (AreaCode = "") Then Worksheets(2).Range(AreaCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) Else Worksheets(2).Range(AreaCol & CStr(Start)).Interior.Color = RGB(0, 255, 0) End If If (TypeCode <> "" And AreaCode <> "" And GroupValue <> "") Then GroupCode = "" lStart = 1 Do Until GroupCode <> "" Or lStart = lRows + 1 If (Worksheets("TagCodes").Range("A" & CStr(lStart)).Value = GroupValue) Then If (Worksheets("TagCodes").Range("B" & CStr(lStart)).Value = TypeCode And Worksheets("TagCodes").Range("C" & CStr(lStart)).Value = AreaCode And Worksheets("TagCodes").Range("D" & CStr(lStart)).Value <> " ") Then Worksheets(2).Range(gValueCol & CStr(Start)).Value = Worksheets("TagCodes").Range("D" & CStr(lStart)).Value GroupCode = Worksheets(2).Range(gValueCol & CStr(Start)).Value Exit Do Else lStart = lStart + 1 End If Else lStart = lStart + 1 End If Loop If (GroupCode = "") Then Worksheets(2).Range(GroupCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) Else Worksheets(2).Range(GroupCol & CStr(Start)).Interior.Color = RGB(0, 255, 0) End If Else Worksheets(2).Range(GroupCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) End If Else Worksheets(2).Range(AreaCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) Worksheets(2).Range(GroupCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) End If Else Worksheets(2).Range(TypeCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) Worksheets(2).Range(AreaCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) Worksheets(2).Range(GroupCol & CStr(Start)).Interior.Color = RGB(255, 0, 0) End If Start = Start + 1 Loop End Function