# EXCEL下使用MATCH函數碰到的問題

excel|函數|問題
Sub USEMATCH()
Dim s_p As String, e_p As String
Dim num As Integer
num = 0
For Each M In Range("a:a")
If M.Value <> "" Then
num = num + 1
Else
Exit For
End If
Next M
erange = "b" & num
erange = "b2:" & erange
N = 1
a = 2
currange = "b" & a
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Columns("A:A").Select
Selection.Insert Shift:=xlToRight '最左插入一列
Set curCell = Worksheets(Sheets(1).Name).Range(currange)
For Each M In Range(erange)
On Error GoTo ErrorHandler
If M.Offset(0, -1).Value <> "" Then GoTo mynext
If M.Offset(0, 1).Value = "" Then GoTo mynext    '目前的儲存格左不為空白/右儲存格內容為空白則轉
s_p = M.Value: e_p = M.Offset(0, 1).Value

pos = Application.WorksheetFunction.Match(e_p, Worksheets(1).Range(erange), 0) '尋找終點在起點列出現的行數
If pos = "" Then
curCell.Offset(0, -1).Value = "NO"
GoTo mynext              '若沒有找到則設為"no"
End If
thenext:
Position = "B" & Trim(Str(pos))                   '定位到所在儲存格
If Range(Position).Offset(0, 1).Value = s_p Then
If Range(Position).Offset(0, -1) = "" Then       '若符合條件則在對應記錄前標記
curCell.Offset(0, -1).Value = N & ".A"
Range(Position).Offset(0, -1).Value = N & ".B"
N = N + 1
Else
curCell.Offset(0, -1).Value = "NO"
End If

Else
If Range(Position).Offset(1, 0).Value = e_p Then
pos = pos + 1
GoTo thenext
Else
curCell.Offset(0, -1).Value = "NO"
End If
End If
myVar = 0

mynext:
a = a + 1
currange = "b" & a
Set curCell = Worksheets(Sheets(1).Name).Range(currange)
Next
ErrorHandler:
curCell.Offset(0, -1).Value = "NO"
Resume Next
End Sub

## E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

## Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

## Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD