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

來源:互聯網
上載者:User
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

表格形式為:A列 和B列.  匹配條件是:按行查詢,若第一行的A列儲存格內容等於另一行B列儲存格內容,就檢查第一行B列儲存格內容是否等於另一行A列儲存格內容,若相等就在這兩行前做標記.否則標記為NO


相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

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

Learn more >

Alibaba Cloud Free Trial

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

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。