Incrementally Update QTY with each MATCH while Looping Challenge?

C

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
601
I'm trying to figure out a way to Incrementally Update the QTY each time a match is found while bouncing it against another sheet within the same workbook.

Here's an example:
Start with "PARTS" sheet, row 2, look for this part # "12345" in the "INVENTORY" sheet, row 2.
If match = turn both part # cells BOLD FONT on both sheets.
Notice that the QTY is 5 on the "INVENTORY" sheet,
COPY the QTY of the "INVENTORY" sheet of '5' to the "PARTS" sheet. (overwrite the '1') this 1st time thru the loop.

Continue to look for that SAME part # on row 3 of the "INVENTORY" sheet.
If no match = continue to row 4 on "INVENTORY" sheet.
If another match is found on row 4 of "INVENTORY" sheet, turn the part # cells BOLD FONT
then SUM the QTY shown on the "INVENTORY" sheet '5' to what was previously pasted on the "PARTS" sheet '5'.
The "PARTS" sheet should now show the updated QTY of '10'

Continue with loop, an look to row 5 of "INVENTORY" sheet for additional matches...
If another match is found on row 5 of "INVENTORY" sheet, turn that part # cell BOLD FONT
then SUM the QTY shown on the "INVENTORY" sheet '15' to what was previously pasted on the "PARTS" sheet '10.
The "PARTS" sheet should now show the updated QTY of '25.
THE GOAL is to CONTINUALLY UPDATE THE QTY OF THE "PARTS" sheet based on matches/qtys reported on "INVENTORY" sheet.

Walk Thru Steps That Occured:
The QTY of 1 got overwritten and changed to 5 on the first match pass...
Then QTY got updated to QTY of 10 (5+5) found on the 2nd match pass...
Then QTY got updated to QTY of 25 (10+15) found on that last match pass..

===============================
...SHEET: "PARTS"
1..DESC......PART#.....QTY....
-----------------------------
2..bolt........12345......1......
3..clamp.....78787.......7......
4..screw.....12345.......9......

===============================
.
===============================

...SHEET: "INVENTORY"
1..QTY....DESC......PART#.....
----------------------------------
2..5......bolt......12345.....
3..3......wing......94566.....
4..5......bolt......12345.....
5..15.....bolt......12345.....
===============================
 
Sort by date Sort by votes
Unless there is a space embedded into the alphanumeric sequence I really have no idea. But that is what it sounds like to me; is it the case that one of the columns has a space between the numbers/letters? Trim and clean won't find those. I guess here's what you could do. Use this function to see if there is a hidden space: =FIND(" ",B8). Place it in a column on row 8 and fill down. I'd be willing to bet the PNs that return zeros also return a 1 with the FIND function.
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
..interesting
The =FIND(" ",B8) produces : #VALUE! all the way down


I'm totally stumped because I've always been able to successfully clean ghost spaces with clean and trim - but this booger is not going ANYwhere -- surely, this has happened before to someone -somewhere -- =-/

Is there code that will essentially copy those 2 rows of data,
paste them as paste+special+something ----
and then re-copy and re-paste them back into their original columns to try to clean them up during a copy/paste functionality?
 
Last edited:
Upvote 0
I even tried taking the whole QTY column of the TO and pasting it into "NOTEPAD"
Then copied/pasted back into the xls as text
Then checked the LEN count and SURE ENOUGH --- it is STILL coming up with 1 extra character!!
ARGGGGGGHHHHHHHHHH! how can this be!?
 
Upvote 0
OK HERE WE GO!!!
Found something that works![/]
Have no idea what all this code means at this point -- but I tested it and it cleaned both the text and numerical!

(My Col G held both quantities and codes ---- both of which had 1 hidden character that TRIM and CLEAN would not get rid of)
This code found that character and elminated it --
Now, when I test with "LEN=(G8)" to see the new count, it is one less!
WOOOO HOOOOO!

Code: 
Sub Mod_111_12_BOM2TO()

'GETS RID OF GHOST CHARACTERS THAT TRIM AND CLEAN WOULD NOT CLEAR!!!

'Sub EveryCharacter()
     
    Dim i As Long
    Dim L As Long
    Dim c As Range
    Dim r As String
    Dim rng As Range
     
     'Range to search/replace
    Set rng = Range("G9:G100")
     
     'Every Cell!
    For Each c In rng
         'Get length of string in cell
        L = Len(c)
         'If blank go next
        If L = 0 Then GoTo phred
         'Every character...
        For i = 1 To L
            r = Mid(c, i, 1)
             'If current char is outside 'normal' ASCII range
            If r < Chr(32) Or r > Chr(126) Then
                 'delete it
                c.Replace what:=r, replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False
            End If
             'else get next character in cell
        Next
phred:
         'Get next cell
    Next c
     
    
End Sub

If you or anyone who's a real vba genious cares to explain ('comment out') this code, I'd love to know what it is doing! (how it is doing it)
THANKS 'pleaseemailme' for all your awesome help!
 
Upvote 0
I'm glad you found some code that is works. It looks like the code is already commented for you. Basically it is looping through all the characters in every cell of the range and if the character value is outside the range of 'normal' characters it deletes it. You could Google ASCII characters - you'll find every character has a numeric value, pretty interesting.

Good luck!
 
Upvote 0
This thing just WON'T DIE... discovered a new issue with this code, but probably not a challenge for you wizards...
The code doesn't know how to act when/if it encounters text/alpha codes within Col G when it goes to get the QTY.
Currently Col G holds both numbers and text codes however, when I test the column all of them are =ISTEXT(xx) "TRUE".
*It seems to SUM the numbers fine as it should -- but did not handle the text code "RHF" as it should...

The code should go get the QTY and bring it over to the other sheet.
If by chance the code finds more than 1 row = to that same part #, it should SUM up all findings and place that total on the other sheet.
This is working fine.... <<<

The code should -- if it encounters text that looks like alpha codes in Col G (should simply copy that text alpha code over to the other sheet into Col E).
This is the part that is not working <<<
I'd mentioned previously that there will never be more than one of these so the code won't ever try to SUM together more than one text cell.

I've discovered that it came across a text alpha code "RGF" and did not know what to do -- so it copied a zero into my recipient sheet whereas it should have copied the code over to Col E of the other sheet....

Anyone know how to update this code to handle the text code found in Col G appropriately by pasting that actual code into Col E without turning it into a zero??

It is pasting a formula into Col E of the "BOM" sheet that doesnt know how to handle the TEXT code...
=SUMIF(TO!$B$8:$B$100,'BOM Worksheet'!P10,TO!$G$8:$G$100)

Needs to be an IF statement embedded in there of some kind:
IF the text looks like a number and can be SUMMED - then bring it over
IF the text looks like real text (in the form of a code "RGH", etc) then, bring it over as is -- don't try to SUM it to anything...


Code: 
Sub Mod_12_BOM2TO()

'GETS RID OF GHOST CHARACTERS THAT TRIM AND CLEAN WOULD NOT CLEAR!!!
'.......this ghost spacing was preventing other code from locating a match when
'.......it tried to compare to Part Numbers...
'.......Matches were clearly present but unrecognized due to the char issue
'.......rendering no matches between the "TO" sheet and the "BOM" sheet
     
 'Sub EveryCharacter()
    Sheets("TO").Select
 
    Dim i As Long
    Dim L As Long
    Dim c As Range
    Dim r As String
    Dim rng As Range
 
     'Range to search/replace
    Set rng = Range("G8:G5000")
    'TO DO THE ENTIRE ACTIVE SHEET USE THIS LINE OF CODE INSTEAD: < < doesnt work =-(
    'Set rng = ActiveSheet.UsedRange    < < doesnt work =-(
 
     'Every Cell!
    For Each c In rng
         'Get length of string in cell
        L = Len(c)
         'If blank go next
        If L = 0 Then GoTo phred
         'Every character...
        For i = 1 To L
            r = Mid(c, i, 1)
             'If current char is outside 'normal' ASCII range
            If r < Chr(32) Or r > Chr(126) Then
                 'delete it
                c.Replace what:=r, replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False
            End If
             'else get next character in cell
        Next
phred:
         'Get next cell
    Next c
    
'------------------------------------------------
'Sub IncrementalMatchSumTotQtyBOM2TO()

'THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH
'OF THE MATCHES FOUND.
'IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'IT SUMS THE TOTAL AND PASTES IT ON THE "BOM" SHEET WHILE USING THE "TO" SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.
'IF A TEXT CODE IS FOUND IN COL G OF "TO", IT SHOULD SIMPLY COPY THAT CODE OVER COL E OF "BOM"
    
    Sheets("BOM Worksheet").Select
    
    Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    
    
    'Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC[11],TO!R8C7:R100C7)"


    
  Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic

'------------------------------------------------
'Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("E5:E" & lRow)
    Dim cel1 As Range
    Dim cel2 As Range
    If Application.WorksheetFunction.CountIf(Range("E5:E" & lRow), 0) >= 1 Then
        MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
    End If
    For Each cel1 In MR
        If cel1 = 0 Then
            cel1.Interior.Color = RGB(255, 0, 0)
        End If
    Next cel1
    If Application.WorksheetFunction.CountIf(Range("C2:C" & lRow), 0) = 0 Then
        'MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")
    End If
    For Each cel2 In MR
        If cel2 > 0 Then
            cel2.Interior.Color = RGB(255, 255, 255)
        End If
    Next cel2
    Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
ChrisOK,
Try this code:

Code: 
Sub Mod_12_BOM2TO()

'GETS RID OF GHOST CHARACTERS THAT TRIM AND CLEAN WOULD NOT CLEAR!!!
'.......this ghost spacing was preventing other code from locating a match when
'.......it tried to compare to Part Numbers...
'.......Matches were clearly present but unrecognized due to the char issue
'.......rendering no matches between the "TO" sheet and the "BOM" sheet
     
 'Sub EveryCharacter()
    Sheets("TO").Select
 
    Dim i As Long
    Dim L As Long
    Dim c As Range
    Dim r As String
    Dim rng As Range
 
     'Range to search/replace
    Set rng = Range("G8:G5000")
    'TO DO THE ENTIRE ACTIVE SHEET USE THIS LINE OF CODE INSTEAD: < < doesnt work =-(
    'Set rng = ActiveSheet.UsedRange    < < doesnt work =-(
 
     'Every Cell!
    For Each c In rng
         'Get length of string in cell
        L = Len(c)
         'If blank go next
        If L = 0 Then GoTo phred
         'Every character...
        For i = 1 To L
            r = Mid(c, i, 1)
             'If current char is outside 'normal' ASCII range
            If r < Chr(32) Or r > Chr(126) Then
                 'delete it
                c.Replace what:=r, replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False
            End If
             'else get next character in cell
        Next
phred:
         'Get next cell
    Next c
    
'------------------------------------------------
'Sub IncrementalMatchSumTotQtyBOM2TO()


'THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH
'OF THE MATCHES FOUND.
'IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'IT SUMS THE TOTAL AND PASTES IT ON THE "BOM" SHEET WHILE USING THE "TO" SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.
'IF A TEXT CODE IS FOUND IN COL G OF "TO", IT SHOULD SIMPLY COPY THAT CODE OVER COL E OF "BOM"
    
    Sheets("BOM Worksheet").Select
    
    Range("E5:E100").NumberFormat = "General"
'    Range("E5").Select
'    ActiveCell.FormulaR1C1 = _
'        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    
    
    'Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
    "=IF(ISTEXT(OFFSET(TO!R1C7,MATCH('BOM Worksheet'!RC[11],TO!R2C2:R50000C2,0)," & _
    "0)),OFFSET(TO!R1C7,MATCH('BOM Worksheet'!RC[11],TO!R2C2:R50000C2,0),0)," & _
    "SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC[11],TO!R8C7:R100C7))"




    
  Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic


'------------------------------------------------
'Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("E5:E" & lRow)
    Dim cel1 As Range
    Dim cel2 As Range
    If Application.WorksheetFunction.CountIf(Range("E5:E" & lRow), 0) >= 1 Then
        MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
    End If
    For Each cel1 In MR
        If cel1 = 0 Then
            cel1.Interior.Color = RGB(255, 0, 0)
        End If
    Next cel1
    If Application.WorksheetFunction.CountIf(Range("C2:C" & lRow), 0) = 0 Then
        'MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")
    End If
    For Each cel2 In MR
        If cel2 > 0 Then
            cel2.Interior.Color = RGB(255, 255, 255)
        End If
    Next cel2
    Application.ScreenUpdating = True


End Sub
 
Upvote 0
OMG!! Now I owe you a drink for sure in April!!!
IT WORKS! - HALLELUAH!!
The only thing it doesn't do it turn that cell w/ the code to red (the analyst needs to be alerted when zeros or codes are present w/ the red)
This is frickin' awesome!! (and was the 2nd of my two biggest head-aches this week!)
You have NO idea how happy I am about now.... Made my day... SERIOUSLY.. lol *happy dancin' - big time*

THANK GOD, one major issue down - one to go and I'm DONE w/ this project (I hope)...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Merge QTY & AMOUNT for each part of item for each range
Replies
2
Views
63
Amer Omar
A
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
463
skillilea
S
Y
  • Question Question
sum total in specific column when it complete 1 quantity in column H
Replies
0
Views
410
yoon
Y
K
  • Question Question
Array SUM or SUMPRODUCT function error
Replies
1
Views
504
KratosZG
K
R
  • Question Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
472
Domenic
D

Forum statistics

Threads
1,223,533
Messages
6,172,886
Members
452,486
Latest member
standw01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top