TAPS_MikeDion
Well-known Member
- Joined
- Aug 14, 2009
- Messages
- 622
- Office Version
- 2011
- Platform
- MacOS
Hi guy's,
This is a question that goes with a routine that sandeep.warrier helped me with previously. If you would like to referrence the original thread, HERE'S the link.
I have the routine below to sort totals, and it works like a charm. I now need to have a ListBox (using MultiSelectMulti) that will allow the user to select the items from the ListBox, and as they check items a total is constantly updated in a TextBox that is below the ListBox.
What it boils down to is the user needs to be able to select a number of items in the list to find out what the total of those items comes to, then they can uncheck the boxes and check others to see what those checked items add up to.
Here is an actual example of the data that the routine checks through:
TOTAL: RJ 596 596 20,500 8,740 294 596 0 0 0 0 0 0
His routine searches through all of the sheets in the workbook to find a match to the three characters after the * in each line above, and then takes the total (which is the number after the fourth space) and puts the total in the cell to the right of the cell it found the match in.
The example above shows that (we call them Trips) Trip 03A has a total of 281 pieces. With all of that being said, is there a way the listbox can populate (holding the Trip number and it's total) as the routine from sandeep.warrier runs, maybe through an array or something?
Here is the routine that extracts and copies over the totals:
Here is one sample sheet with Trips and totals:
I do realize that this is a lot, but if someone could help me with this I'd greatly appreciate it!
Thanks all,
Mike
<!-- / message --><!-- sig -->
This is a question that goes with a routine that sandeep.warrier helped me with previously. If you would like to referrence the original thread, HERE'S the link.
I have the routine below to sort totals, and it works like a charm. I now need to have a ListBox (using MultiSelectMulti) that will allow the user to select the items from the ListBox, and as they check items a total is constantly updated in a TextBox that is below the ListBox.
What it boils down to is the user needs to be able to select a number of items in the list to find out what the total of those items comes to, then they can uncheck the boxes and check others to see what those checked items add up to.
Here is an actual example of the data that the routine checks through:
RECORD-JOURNAL
PRINT DATE: MON 09/14/2009 5:19 PM TRUCK MANIFEST 090915MANIFEST.TXT
PRINTED BY: GRATT PUBLISHING DATE: 09/15/09 PAGE 55
TRUCK RECAP BY PRODUCT
NUMBER TOTAL KEY STANDARD KEY BULK BULK BULK BULK NUMBER THROW
TRUCK PRODUCT DROPS WRAPS DRAW DRAW BUNDLES BUNDLES DRAW KEY DRAW STANDARDS KEYS HAND-TIE OFFS
----- ------- ----- ------- ------- ------- -------- -------------- -------- --------- ------- ------------ -----
*001 RJ 17 17 1,433 393 26 17 0 0 0 0 0 0
*002 RJ 17 17 1,483 443 26 17 0 0 0 0 0 0
*003 RJ 24 24 1,919 479 36 24 0 0 0 0 0 0
*004 RJ 15 15 1,330 370 24 15 0 0 0 0 0 0
*01A RJ 8 8 288 168 3 8 0 0 0 0 0 0
*02A RJ 5 5 143 143 0 5 0 0 0 0 0 0
*03A RJ 8 8 281 201 2 8 0 0 0 0 0 0
*012 RJ 12 12 250 130 3 12 0 0 0 0 0 0
*040 RJ 62 62 1,008 888 3 62 0 0 0 0 0 0
*041 RJ 43 43 628 588 1 43 0 0 0 0 0 0
*042 RJ 39 39 215 215 0 39 0 0 0 0 0 0
*008 RJ 20 20 2,403 563 46 20 0 0 0 0 0 0
*081 RJ 69 69 730 650 2 69 0 0 0 0 0 0
*023 RJ 40 40 1,078 398 17 40 0 0 0 0 0 0
*005 RJ 20 20 1,514 474 26 20 0 0 0 0 0 0
*007 RJ 23 23 2,065 465 40 23 0 0 0 0 0 0
*010 RJ 16 16 870 310 14 16 0 0 0 0 0 0
*016 RJ 10 10 836 196 16 10 0 0 0 0 0 0
*027 RJ 16 16 158 118 1 16 0 0 0 0 0 0
*028 RJ 6 6 100 60 1 6 0 0 0 0 0 0
*030 RJ 6 6 88 48 1 6 0 0 0 0 0 0
*024 RJ 1 1 160 40 3 1 0 0 0 0 0 0
*025 RJ 65 65 830 790 1 65 0 0 0 0 0 0
*026 RJ 54 54 690 610 2 54 0 0 0 0 0 0
------ ------- ------- ------- -------- ------- ------- -------- --------- ------- ------------ -----
GRAND
PRINT DATE: MON 09/14/2009 5:19 PM TRUCK MANIFEST 090915MANIFEST.TXT
PRINTED BY: GRATT PUBLISHING DATE: 09/15/09 PAGE 55
TRUCK RECAP BY PRODUCT
NUMBER TOTAL KEY STANDARD KEY BULK BULK BULK BULK NUMBER THROW
TRUCK PRODUCT DROPS WRAPS DRAW DRAW BUNDLES BUNDLES DRAW KEY DRAW STANDARDS KEYS HAND-TIE OFFS
----- ------- ----- ------- ------- ------- -------- -------------- -------- --------- ------- ------------ -----
*001 RJ 17 17 1,433 393 26 17 0 0 0 0 0 0
*002 RJ 17 17 1,483 443 26 17 0 0 0 0 0 0
*003 RJ 24 24 1,919 479 36 24 0 0 0 0 0 0
*004 RJ 15 15 1,330 370 24 15 0 0 0 0 0 0
*01A RJ 8 8 288 168 3 8 0 0 0 0 0 0
*02A RJ 5 5 143 143 0 5 0 0 0 0 0 0
*03A RJ 8 8 281 201 2 8 0 0 0 0 0 0
*012 RJ 12 12 250 130 3 12 0 0 0 0 0 0
*040 RJ 62 62 1,008 888 3 62 0 0 0 0 0 0
*041 RJ 43 43 628 588 1 43 0 0 0 0 0 0
*042 RJ 39 39 215 215 0 39 0 0 0 0 0 0
*008 RJ 20 20 2,403 563 46 20 0 0 0 0 0 0
*081 RJ 69 69 730 650 2 69 0 0 0 0 0 0
*023 RJ 40 40 1,078 398 17 40 0 0 0 0 0 0
*005 RJ 20 20 1,514 474 26 20 0 0 0 0 0 0
*007 RJ 23 23 2,065 465 40 23 0 0 0 0 0 0
*010 RJ 16 16 870 310 14 16 0 0 0 0 0 0
*016 RJ 10 10 836 196 16 10 0 0 0 0 0 0
*027 RJ 16 16 158 118 1 16 0 0 0 0 0 0
*028 RJ 6 6 100 60 1 6 0 0 0 0 0 0
*030 RJ 6 6 88 48 1 6 0 0 0 0 0 0
*024 RJ 1 1 160 40 3 1 0 0 0 0 0 0
*025 RJ 65 65 830 790 1 65 0 0 0 0 0 0
*026 RJ 54 54 690 610 2 54 0 0 0 0 0 0
------ ------- ------- ------- -------- ------- ------- -------- --------- ------- ------------ -----
GRAND
TOTAL: RJ 596 596 20,500 8,740 294 596 0 0 0 0 0 0
His routine searches through all of the sheets in the workbook to find a match to the three characters after the * in each line above, and then takes the total (which is the number after the fourth space) and puts the total in the cell to the right of the cell it found the match in.
The example above shows that (we call them Trips) Trip 03A has a total of 281 pieces. With all of that being said, is there a way the listbox can populate (holding the Trip number and it's total) as the routine from sandeep.warrier runs, maybe through an array or something?
Here is the routine that extracts and copies over the totals:
Code:
Sub InsertTotals_Click()
Dim i As Long
Dim sInput As String
Dim sSearchResult As String
Dim rng As Range
Dim FinalResult As Variant
Sheets("MANIFEST.TXT").Select
Range("B8").Select
While Mid(ActiveCell.Offset(0, -1).Value, 6, 1) = "R"
ActiveCell.Value = Left(ActiveCell.Offset(0, -1).Value, 7)
ActiveCell.Offset(1, 0).Select
Wend
For i = 3 To (ThisWorkbook.Worksheets.Count - 6)
Sheets(i).Select
If ActiveSheet.Name <> "MANIFEST.TXT" Then
Range("C28").Select
While ActiveCell.Offset(0, -1).Value <> ""
sInput = Right(ActiveCell.Offset(0, -1).Value, 3)
Sheets("MANIFEST.TXT").Select
With Range("B8", Range("B" & Rows.Count).End(xlUp))
Set rng = .Find(what:=sInput, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
sSearchResult = rng.Offset(0, -1).Value
Sheets(i).Select
FinalResult = Split(sSearchResult, " ")
ActiveCell.Value = FinalResult(4)
End If
End With
Sheets(i).Select
ActiveCell.Offset(1, 0).Select
Wend
End If
Next i
End Sub
Here is one sample sheet with Trips and totals:
I do realize that this is a lot, but if someone could help me with this I'd greatly appreciate it!
Thanks all,
Mike
<!-- / message --><!-- sig -->