Hello,
I give 1500fg for the solution ! If you need the excel files, contact me.
I already solved the first issue through an excel forum, however I still post it as an introduction: (you could go below the ---------)
I work with inflation data and have different codes, the number is always the industry code, next to it is the industry name.
01 Industry A
02 Industry B
03 Industry C
04 Industry D
05 Industry E
.
.
15 Industry O
Now there are sub-categories within those industries:
Structure: 00(Industry)0(Sub-category)0(Sub-Sub-category)
011 Industry A - Sub-category A
012 Industry A - Sub-category B
0121 Industry A - Sub-category B - Sub-Sub-category A
0122 Industry A - Sub-category B - Sub-Sub-category B
021 Industry B - Sub-category A
031 -..-
041 -..-
051 -..-
0511 Industry E - Sub-category A - Sub-Sub-category A
.
.
1543 Industry O - Sub-category D - Sub-Sub-category C
As you can see, it is a nested structure.
Now it looks like that:
I have a list of available industries for each country.
1.) I choose 3 countries (with 3 Dropdowns): UK, FRA, GER
2.) I choose 1 industry code (1 Dropdown): p.e. 1543
3.) For UK, FRA, GER I want to write the most exact category possible into another cell.
UK has data until 1543 => Extract 1543
FRA has data until 154 => Extract 154
GER has data until 15 => Extract 15
--------------------------------- until here it's solved! I got help already so that it works, when everything is on the same excel sheet.
However I want to split everything into 3 sheets (as the real data is alot bigger and it gets messy otherwise!)
What I want is the following:
1)Choose countries/industries from Sheet1 (in a Combobox)
2)Take the list from Sheet2
3)Make the calculations / extract in Sheet3
Could somebody please help me?
Thats the code that works if everything is in one sheet:
Code
Option Explicit
Private Sub ComboBox4_Change()
Dim arrCode() As Variant
Dim arrList As Variant
Dim strCode As String
Dim Res As Variant
Dim idx As Long
Dim I As Long
Dim J As Long
Dim cnt As Long
arrList = ComboBox4.List
For I = LBound(arrList) To UBound(arrList)
arrList(I, 0) = CStr(arrList(I, 0))
Next I
idx = ComboBox4.ListIndex
cnt = 1
If idx <> -1 Then
For I = Len(ComboBox4.List(idx)) To 2 Step -1
ReDim Preserve arrCode(1 To 3, 1 To cnt)
strCode = Left(ComboBox4.List(idx), I)
Res = Application.Match(strCode, arrList, 0)
If Not IsError(Res) Then
For J = 1 To 3
arrCode(J, cnt) = IIf(Range("A" & Res + 1).Offset(, J).Value = "YES", strCode, "-")
Next J
cnt = cnt + 1
End If
Next I
End If
If cnt > 1 Then
Range("F13", Range("F13").End(xlDown).Offset(, 2)).ClearContents
With Range("F13").Resize(UBound(arrCode, 2), UBound(arrCode))
.NumberFormat = "@"
.Value = Application.Transpose(arrCode)
End With
End If
End Sub
I tried to transform it into the 3-sheets-solution, but it's not working!
This is my try: (note that in the new file its Combobox1, not Combobox4, thats not a mistake.)
Code
Option Explicit
Private Sub ComboBox1_Change()
Dim arrCode() As Variant
Dim arrList As Variant
Dim strCode As String
Dim Res As Variant
Dim idx As Long
Dim I As Long
Dim J As Long
Dim cnt As Long
arrList = Worksheets("Choose").ComboBox1.List
For I = LBound(arrList) To UBound(arrList)
arrList(I, 0) = CStr(arrList(I, 0))
Next I
idx = Worksheets("Choose").ComboBox1.ListIndex
cnt = 1
If idx <> -1 Then
For I = Len(Worksheets("Choose").ComboBox1.List(idx)) To 2 Step -1
ReDim Preserve arrCode(1 To 3, 1 To cnt)
strCode = Left(Worksheets("Choose").ComboBox1.List(idx), I)
Res = Application.Match(strCode, arrList, 0)
If Not IsError(Res) Then
For J = 1 To 3
arrCode(J, cnt) = IIf(Range("A" & Res + 1).Offset(, J).Value = "YES", strCode, "-")
Next J
cnt = cnt + 1
End If
Next I
End If
If cnt > 1 Then
With Sheets("Calculations")
Range("F13", Range("F13").End(xlDown).Offset(, 2)).ClearContents
With Range("F13").Resize(UBound(arrCode, 2), UBound(arrCode))
.NumberFormat = "@"
.Value = Application.Transpose(arrCode)
End With
End With
End If
End Sub
I think the problem is that it doesn't see the list of the combobox, which is in another sheet now.
Screenshot of everything on one sheet (WORKING):
http://oi58.tinypic.com/28ahwk9.jpgScreenshots of split into three sheets (NOT WORKING)
part1:http://oi62.tinypic.com/2qa1lhd.jpg
part2:http://oi60.tinypic.com/1ih9jr.jpg
part3:http://oi60.tinypic.com/2q35ef9.jpg
Please please help me
Best regards and many thanks.