louieferric
New Member
- Joined
- Jul 6, 2016
- Messages
- 2
Hello guys first post here,
So, I have a list of automotive parts, their start of production, and the capacity of the line like this:
Current Part W Part X Part Y Part Z
July, 2016 August, 2016 December, 2016 March, 2017 June, 2018
80% 81% 87% 95% 105%
Here's my code
Dim totalrange as Range
Set totalrange = Range("M1:DA1")
Dim p As Long
Dim tryingrange As Range
Dim thename As Range
Dim thedate As Range
Dim thecap As Range
Dim currentcap As Range
Set currentcap = Range("M1:M3")
For p = 0 To SelectedListBox.ListCount - 1 'user-defined listbox with parts they want to see on the graph (and which ones they don't)
For Each c In totalrange.Cells 'For each cell in the range where ALL the parts names are located
If c.Text = SelectedListBox.List(p) Then 'If the text in that cell in the range matches with the ListBox value then
findingnameo = Sheets("The Master").Range("M1:DA1").Find(c.Value, searchdirection:=xlNext, SearchOrder:=xlByColumns).Column
'Finds the column number
Set thename = Cells(1, findingnameo) 'Gets the name of the aprt, the date of production, and the capacity of that part
Set thedate = Cells(2, findingnameo)
Set thecap = Cells(3, findingnameo)
Set workingrange = Union(thename, thedate, thecap) 'Combines it into one range for the part
Set actualcapacity = Union(currentcap, workingrange) 'This should just give me one big range for all the selected parts
End If
Next c
Next p
Essentially, i have a two (2) listboxes, one that contains all the parts with their respective information, and one where the user selects which one they actually want to see on the bar graph (for what if Part X was run on another line, etc). My code for every list item in the user-defined list, it looks for that part name in the spreadsheet where the information is. if it matches with an item from the list box, it creates a range that includes the part name, date of production, and capacity for that part.
After it finishes the For loops, i want to have one range of all the selected parts. But it only gives me the LAST part selected and omits all in between.
Please help!
Thank you and sorry for the novice posting....and code.
So, I have a list of automotive parts, their start of production, and the capacity of the line like this:
Current Part W Part X Part Y Part Z
July, 2016 August, 2016 December, 2016 March, 2017 June, 2018
80% 81% 87% 95% 105%
Here's my code
Dim totalrange as Range
Set totalrange = Range("M1:DA1")
Dim p As Long
Dim tryingrange As Range
Dim thename As Range
Dim thedate As Range
Dim thecap As Range
Dim currentcap As Range
Set currentcap = Range("M1:M3")
For p = 0 To SelectedListBox.ListCount - 1 'user-defined listbox with parts they want to see on the graph (and which ones they don't)
For Each c In totalrange.Cells 'For each cell in the range where ALL the parts names are located
If c.Text = SelectedListBox.List(p) Then 'If the text in that cell in the range matches with the ListBox value then
findingnameo = Sheets("The Master").Range("M1:DA1").Find(c.Value, searchdirection:=xlNext, SearchOrder:=xlByColumns).Column
'Finds the column number
Set thename = Cells(1, findingnameo) 'Gets the name of the aprt, the date of production, and the capacity of that part
Set thedate = Cells(2, findingnameo)
Set thecap = Cells(3, findingnameo)
Set workingrange = Union(thename, thedate, thecap) 'Combines it into one range for the part
Set actualcapacity = Union(currentcap, workingrange) 'This should just give me one big range for all the selected parts
End If
Next c
Next p
Essentially, i have a two (2) listboxes, one that contains all the parts with their respective information, and one where the user selects which one they actually want to see on the bar graph (for what if Part X was run on another line, etc). My code for every list item in the user-defined list, it looks for that part name in the spreadsheet where the information is. if it matches with an item from the list box, it creates a range that includes the part name, date of production, and capacity for that part.
After it finishes the For loops, i want to have one range of all the selected parts. But it only gives me the LAST part selected and omits all in between.
Please help!
Thank you and sorry for the novice posting....and code.
