[算表] vba迴圈取代呼叫表格問題?

看板Office作者ljuber (美少婦與女兒同學的故事)時間6年前 (2019/08/21 14:01)推噓0(0推 0噓 7→)

留言7則, 2人參與討論串1/1

軟體:excel 版本:任何版本 If (MsgBox("是否執行取代?", vbYesNo) = vbNo) Then Exit Sub Application.ScreenUpdating = False '巨集執行時不更新螢幕 Dim openfile$, filepath$, thiswrkname$ Dim FileChosen As Integer Dim ff As String Dim MM Dim diaFolder As FileDialog thiswrkname = ThisWorkbook.Name '開啟資料夾 Set fd = Application.FileDialog(msoFileDialogFolderPicker) fd.AllowMultiSelect = False FileChosen = fd.Show '沒有選資料夾離開程式 If FileChosen <> -1 Then MsgBox "你沒有選擇資料夾" Exit Sub Else filepath = fd.SelectedItems(1) & "\" End If openfile = Dir(filepath & "*.xls*") Application.DisplayAlerts = False '資料夾沒檔案離開程式 If openfile = "" Then MsgBox "資料夾內沒有要處理檔案，請確認" Exit Sub End If ==== 主要是下面這段: '---- Do While openfile <> "" Workbooks.Open filepath & openfile Workbooks(thiswrkname).Activate Sheets("機構代號").Select For i = 1 To 63 XX = Range("A" & i) YY = Range("B" & i) Workbooks(openfile).Activate For j = 1 To 100 If Range("B" & j) = XX Then Range("C" & j) = YY Next j Next i Workbooks(openfile).Save Workbooks(openfile).Close openfile = Dir Workbooks(thiswrkname).Activate Sheets("機構代號").Select Loop MsgBox "已經將機構名稱改正確" End Sub ==== 大概就是有個表格金融機構代號要回傳到另外很多檔案去我上面那樣寫都只有第一個會改到不知道我那邊邏輯想錯... 檔案跟範例檔案在這邊 https://drive.google.com/open?id=1Sm6oBuiakIprjdwnO-0sEFIdWad8OKiv -- http://www.youtube.com/watch?v=jx-dxHXwFAM

http://www.youtube.com/watch?v=-ZGQxRIn-7o

http://a.imageshack.us/img195/2903/img5738m.jpg

豆起相思，其生南國，花開堪折，莫待落蒂，妹上心頭，如獲至寶。 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 60.250.67.78 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1566367284.A.A79.html