123
返回列表 上一主題 發帖

[發問] 搜尋、比對，再複製過來的功能

iceandy6150

帖子
129 
主題
25 
精華
0 
積分
159 
點名
0  
作業系統
win7 
軟體版本
office2010 
閱讀權限
20 
性別
男 
註冊時間
2011-12-24 
最後登錄
2022-12-12 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2019-5-22 00:32 | 只看該作者

[發問] 搜尋、比對，再複製過來的功能

大家好，我有一個功能想做，但是搜尋比對我不會寫


我會同時開三個檔案
一個是測試檔，裡面有按鈕，這邊也會列出所要的準則

第二個是資料檔
第三個是尺寸檔
共同點是學號
有點像ACCESS的主鍵

測試檔中 會給  所需要的學號   跟這個學號所要查出來的資訊
然後按鈕按下後  程式可以去搜尋比對  把要的資料填進來測試檔中


因為同時開好三個檔
所以直接呼叫是可以取得資料的

但是搜尋比對，我就不會了
再請各位大大幫忙，謝謝

EXCEL問題.rar (30.49 KB)
收藏 分享
哈囉~大家好呀

jeffrey628litw

帖子
228 
主題
62 
精華
0 
積分
364 
點名
20  
作業系統
Win 10 
軟體版本
Office 2007 & 2010 
閱讀權限
20 
性別
男 
註冊時間
2011-3-5 
最後登錄
2024-9-16 

2011兔年全勤獎章
2#
發表於 2019-5-22 13:13 | 只看該作者
回復 1# iceandy6150


    放同一個檔案中可以嗎?

這2個檔案你參考一下，裡面設條件位置不同，你可以自己參考VBA研究如何修改放條件的位置。


VBA - 設多條件從資料庫中撈出結果(常用).rar (21.76 KB)

TOP

Kubi

帖子
163 
主題
1 
精華
0 
積分
170 
點名
0  
作業系統
Window 7 
軟體版本
Office 2007 
閱讀權限
20 
性別
男 
註冊時間
2010-9-5 
最後登錄
2022-7-20 
3#
發表於 2019-5-22 14:11 | 只看該作者
回復 1# iceandy6150
請參考
  1. Private Sub CommandButton1_Click()
  2.     Dim arr
  3.     Dim brr()
  4.     Dim d As Object
  5.     Set d = CreateObject("Scripting.Dictionary")
  6.     Application.ScreenUpdating = False
  7.     ar = Array("資料.xlsx", "尺寸.xlsx")
  8.     For Each book In ar
  9.         Workbooks.Open ThisWorkbook.Path & "\" & book
  10.         arr = ActiveSheet.[A1].CurrentRegion
  11.         ActiveWorkbook.Close 0
  12.         For i = 2 To UBound(arr)
  13.             For j = 2 To UBound(arr, 2)
  14.                 d(arr(i, 1) & arr(1, j)) = arr(i, j)
  15.             Next j
  16.         Next i
  17.     Next book
  18.     arr = ActiveSheet.[A1].CurrentRegion
  19.     ReDim brr(1 To UBound(arr) - 1, 1 To UBound(arr, 2) - 1)
  20.     For i = 2 To UBound(arr)
  21.         For j = 2 To UBound(arr, 2)
  22.             brr(i - 1, j - 1) = d(arr(i, 1) & arr(1, j))
  23.         Next j
  24.     Next i
  25.     [B2].Resize(UBound(brr), UBound(brr, 2)) = brr
  26.     Application.ScreenUpdating = True
  27.     Erase brr
  28.     Set d = Nothing
  29.     arr = ""
  30. End Sub
複製代碼
注意：本程式會自動開啟兩個資料檔來比對，因此執行前不需先開啟資料檔案。

TOP

iceandy6150

帖子
129 
主題
25 
精華
0 
積分
159 
點名
0  
作業系統
win7 
軟體版本
office2010 
閱讀權限
20 
性別
男 
註冊時間
2011-12-24 
最後登錄
2022-12-12 
4#
發表於 2019-5-22 22:53 | 只看該作者
回復 2# jeffrey628litw


    感謝您的回覆，因為我的資料是在不同的檔案中，所以發問才會使用不同檔案
   我會再自己試看看的，謝謝您
哈囉~大家好呀

TOP

iceandy6150

帖子
129 
主題
25 
精華
0 
積分
159 
點名
0  
作業系統
win7 
軟體版本
office2010 
閱讀權限
20 
性別
男 
註冊時間
2011-12-24 
最後登錄
2022-12-12 
5#
發表於 2019-5-22 22:55 | 只看該作者
回復 3# Kubi


    哇~~ 你這個還可以自動開另外的檔案，然後還可以自動關檔
   太棒了啦，這招我一定要學起來
   感謝您的分享，功能完全符合我的需求
    而且不需要寫很多行就搞定了
    我光是陣列那邊就不行了....
    再次感謝
哈囉~大家好呀

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
6#
發表於 2019-5-23 06:10 | 只看該作者
回復 5# iceandy6150
VBA的可用不同的寫法,來達到同一效果
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim Rng() As Range, Ar(), xR As Variant, xC As Variant, i As Integer, ii As Integer
  4.     Dim xRng As Range
  5.     Application.ScreenUpdating = False
  6.     Ar = Array("測試.XLSM", "尺寸.XLSX", "資料.XLSX")
  7.     ReDim Rng(UBound(Ar))       '** Rng 重置元素與 Ar 一樣多
  8.     For i = 0 To UBound(Ar)
  9.         '**Workbooks(Ar(0)).Path ** 修改為 尺寸 , 資料 檔案的正確資料夾位置**
  10.         If i > 0 Then Workbooks.Open (Workbooks(Ar(0)).Path & "\" & Ar(i)) '**開啟檔案
  11.         With Workbooks(Ar(i))
  12.             Set Rng(i) = .Sheets(1).Range("A1").CurrentRegion   '**設定個檔案的資料範圍
  13.         End With
  14.     Next
  15.     With Rng(0)                         '**測試.XLSM 清除要導入資料的範圍
  16.         .Range(.Cells(2, 2), .Cells(.Rows.Count, .Columns.Count)) = ""
  17.     End With
  18.     Set xRng = Rng(0).Cells(2, 1)       '**測試.XLSM: 第一個 學號
  19.     Ar = Rng(0)                         '**測試.XLSM: 範圍資料導入陣列
  20.     Do While xRng <> ""                 '迴圈: 學號的搜尋
  21.         For ii = 1 To UBound(Rng)
  22.             xR = Application.Match(xRng, Rng(ii).Columns(1), 0) '尺寸,資料 中搜尋 學號(的列號)
  23.             If Not IsError(xR) Then                             '**搜尋到 學號(的列號)
  24.                 For i = 2 To Rng(0).Rows(1).Cells.Count         '**測試 欄位名稱
  25.                     '**xC 傳回是否搜尋到 欄位名稱
  26.                     xC = Application.Match(Rng(0).Cells(1, i), Rng(ii).Rows(1).Cells, 0)
  27.                     If Not IsError(xC) Then Ar(xRng.Row, i) = Rng(ii).Cells(xR, xC) '**導入資料到陣列
  28.                 Next
  29.             End If
  30.         Next
  31.         Set xRng = xRng.Offset(1)           '**測試.XLSM: 下一個 學號
  32.     Loop
  33.     For i = 1 To UBound(Rng)
  34.         Rng(i).Parent.Parent.Close          '**關閉 "尺寸.XLSX", "資料.XLSX"
  35.     Next
  36.     Rng(0) = Ar                             '**陣列資料導入測試.XLSM的範圍
  37.     Application.ScreenUpdating = True
  38. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

iceandy6150

帖子
129 
主題
25 
精華
0 
積分
159 
點名
0  
作業系統
win7 
軟體版本
office2010 
閱讀權限
20 
性別
男 
註冊時間
2011-12-24 
最後登錄
2022-12-12 
7#
發表於 2019-5-24 00:09 | 只看該作者
回復 6# GBKEE


    感謝分享另一做法，今天把K大的程式碼印出來慢慢看
    發現，真的很神奇，為什麼都沒有比對的東西在? 就可以做到效果?
    我心裡面起初也是在想  應該要用個 IF 啦  FIND啦  之類的
    不然怎麼知道  "測試"的學號  跟另外兩個檔案的學號  一不一樣
    一樣才複製  不一樣就不複製
   
<一>K大居然用三個迴圈就搞定了，我無法理解啊......(太高深了)

   然後我今天自己嘗試要寫一個小程式
   從"測試"裡面設一按鈕  按下後  會開一個A檔案   自動計算檔案中的B欄有幾列資料
   然後我就設定一個陣列  要把B欄的資料每一列存起來

   假設B欄判斷完有12筆資料  我的變數c就等於12
   但是我Dim Arry(1,c)  我要一維12格的陣列
   就是會出錯  跟我說一定要常數
   那我先 Dim Arry()   然後  c=1   指定Arry(1,c) = 10   這樣也錯  (崩潰....)

    <二>想順便請教  到底要怎麼設定動態的陣列呀?

<三>這是一個變形的比對需求
比對的順序在  "資料"檔案中
但是 "測試"檔案中，因為輸入需要比對的學號進來，不一定都是由小到大
可能0005  再來0001  再來0003
那我希望在按下按鈕後   能自動排成0001  0003  0005的順序  把資料放進 "測試"檔案中

甚至  主索引鍵  若是改成以名字來當主要對照
就是在"測試"檔案中    只有輸入  名字   沒有學號
一樣能以 "資料"檔案中的順序來排
並複製過來 "測試"檔案裡

有辦法嗎?

以上，謝謝
哈囉~大家好呀

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
8#
發表於 2019-5-24 08:17 | 只看該作者
回復 7# iceandy6150

排序的問題,你可用錄製巨集練習看看
  1. Option Explicit
  2. Sub Ex()
  3.     Dim ar(), c As Integer, i As Integer
  4.     '**ReDim 陳述式 在程序層次中用來重新配置動態陣列變數的儲存空間。
  5.     ReDim ar(0 To 2)
  6.     For i = 0 To UBound(ar)
  7.         ar(i) = Chr(65) & i
  8.     Next
  9.     MsgBox UBound(ar) & vbLf & Join(ar, ",")
  10.     c = 8
  11.     ReDim ar(1 To c)
  12.     For i = 1 To UBound(ar) Step 2
  13.         ar(i) = Chr(66) & i
  14.     Next
  15.    
  16.     MsgBox UBound(ar) & vbLf & Join(ar, " , ")
  17.     ReDim Preserve ar(1 To c + 10)
  18.     '**  Preserve 選擇性引數。當改變原有陣列最後一維的大小時，仍然保有原來的資料的關鍵字
  19.     For i = c + 1 To UBound(ar) Step 3
  20.         ar(i) = i & Chr(67)
  21.    
  22.     Next
  23.     MsgBox UBound(ar) & vbLf & Join(ar, ",,")
  24.    
  25. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

iceandy6150

帖子
129 
主題
25 
精華
0 
積分
159 
點名
0  
作業系統
win7 
軟體版本
office2010 
閱讀權限
20 
性別
男 
註冊時間
2011-12-24 
最後登錄
2022-12-12 
9#
發表於 2019-5-29 22:48 | 只看該作者
回復 8# GBKEE


    謝謝~ 我再試試

   初步使用變數視窗  是知道先把所有資料放進一個大陣列

  然後再去第二個檔案打開

  但是沒有 IF來做比較，怎麼知道第二個檔案要放什麼呢

使用錄製巨集要怎麼看?  把程式碼貼進去按鈕嘛? 怎麼看它自己一步一步跑?
哈囉~大家好呀

TOP

准提部林

帖子
2834 
主題
10 
精華
0 
積分
2890 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-11-23 
10#
發表於 2019-5-30 10:31 | 只看該作者
如果只是簡單查詢, 資料也不多, 可用公式:
Sub 巨集1()
P$ = ThisWorkbook.Path & "\"
With Range("D2", Cells(Rows.Count, 1).End(xlUp)(2, 2))
     .Columns(1) = "=VLOOKUP(A2,'" & P & "[資料.xlsx]工作表1'!A:D,2,)"
     .Columns(3) = "=VLOOKUP(A2,'" & P & "[資料.xlsx]工作表1'!A:D,4,)"
     .Columns(2) = "=VLOOKUP(A2,'" & P & "[尺寸.xlsx]工作表1'!A:D,2,)"
     .Value = .Value
     .Replace "#N/A", ""
End With
End Sub

TOP

        靜思自在 : 【為善競爭】人生要為善競爭，分秒必爭。
123
返回列表 上一主題