Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using match function
I tried using the following code, but the match function is returning
nothing. Im stuck on this one. Any ideas out there? Option Explicit Sub total_count() Dim rngSub1 As Range Dim rngSub2 As Range Dim Cell As Range Dim i As Integer Dim x As Range Dim y As Integer Set rngSub1 = Sheets(1).Range("F:F").SpecialCells(xlCellTypeCons tants, 23) Set rngSub2 = Sheets(1).Range("C:C").SpecialCells(xlCellTypeCons tants, 23) For Each Cell In rngSub1 On Error Resume Next x = Application.WorksheetFunction.Match(Cell, rngSub2, 0) Debug.Print x If Not x Is Nothing Then y = x.Offset(0, 1).Value y = y + 1 x.Offset(0, 1).Value = y Else: MsgBox "NO MATCH" End If Next Cell End Sub "AD108" wrote in message ... I am trying to write a simple solution to do the following. I am checking in orders. Each case has a UPC that I can scan. When I am done scanning each item, I want to match the results against an invoice. (Each time a UPC is read in the "scanned list" , it needs to add "1" to the cell offset from the cell that contains the corresponding UPC in the invoice list. I've been messing around with the find method for a while, but havn't figured out a way to to make it work. Any help would be greatly appreciated. Thanks in advance. INVOICE UPC's SCANNED UPC's 252785101512 252785101512 147009457329 147009457329 991207428183 991207428183 859479181683 859479181683 156370309341 156370309341 924135041065 924135041065 141565295381 141565295381 524092935026 524092935026 328041893778 328041893778 910765794208 910765794208 842191913397 842191913397 378108475997 378108475997 614669765989 614669765989 883098777207 883098777207 124417951086 124417951086 120024611842 120024611842 973039897136 973039897136 436156773394 436156773394 706998507158 706998507158 643665794475 643665794475 653094538041 653094538041 252785101512 147009457329 991207428183 859479181683 156370309341 924135041065 141565295381 524092935026 328041893778 910765794208 842191913397 378108475997 614669765989 883098777207 124417951086 120024611842 973039897136 436156773394 706998507158 643665794475 653094538041 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using match function
Try this
Sub total_count() Dim rngSub1 As Range Dim rngSub2 As Range Dim Cell As Range Dim i As Integer Dim x As Long Dim y As Integer Set rngSub1 = Sheets(1).Range("F:F").SpecialCells(xlCellTypeCons tants, 23) Set rngSub2 = Sheets(1).Range("C:C").SpecialCells(xlCellTypeCons tants, 23) For Each Cell In rngSub1 On Error Resume Next x = 0 x = Application.WorksheetFunction.Match(Cell, rngSub2, 0) Debug.Print x If x < 0 Then y = rngSub2.Cells(x, 2).Value y = y + 1 rngSub2.Cells(x, 2).Value = y Else: MsgBox "NO MATCH" End If Next Cell End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "AD108" wrote in message ... I tried using the following code, but the match function is returning nothing. Im stuck on this one. Any ideas out there? Option Explicit Sub total_count() Dim rngSub1 As Range Dim rngSub2 As Range Dim Cell As Range Dim i As Integer Dim x As Range Dim y As Integer Set rngSub1 = Sheets(1).Range("F:F").SpecialCells(xlCellTypeCons tants, 23) Set rngSub2 = Sheets(1).Range("C:C").SpecialCells(xlCellTypeCons tants, 23) For Each Cell In rngSub1 On Error Resume Next x = Application.WorksheetFunction.Match(Cell, rngSub2, 0) Debug.Print x If Not x Is Nothing Then y = x.Offset(0, 1).Value y = y + 1 x.Offset(0, 1).Value = y Else: MsgBox "NO MATCH" End If Next Cell End Sub "AD108" wrote in message ... I am trying to write a simple solution to do the following. I am checking in orders. Each case has a UPC that I can scan. When I am done scanning each item, I want to match the results against an invoice. (Each time a UPC is read in the "scanned list" , it needs to add "1" to the cell offset from the cell that contains the corresponding UPC in the invoice list. I've been messing around with the find method for a while, but havn't figured out a way to to make it work. Any help would be greatly appreciated. Thanks in advance. INVOICE UPC's SCANNED UPC's 252785101512 252785101512 147009457329 147009457329 991207428183 991207428183 859479181683 859479181683 156370309341 156370309341 924135041065 924135041065 141565295381 141565295381 524092935026 524092935026 328041893778 328041893778 910765794208 910765794208 842191913397 842191913397 378108475997 378108475997 614669765989 614669765989 883098777207 883098777207 124417951086 124417951086 120024611842 120024611842 973039897136 973039897136 436156773394 436156773394 706998507158 706998507158 643665794475 643665794475 653094538041 653094538041 252785101512 147009457329 991207428183 859479181683 156370309341 924135041065 141565295381 524092935026 328041893778 910765794208 842191913397 378108475997 614669765989 883098777207 124417951086 120024611842 973039897136 436156773394 706998507158 643665794475 653094538041 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the second match using the match function | Excel Worksheet Functions | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |