Option Explicit Public Sub 新比較() Dim ssh As Worksheet Dim msh As Worksheet Dim sbsh As Worksheet Dim wrow As Long Dim wcol As Long Dim wcols As String Dim trow As Long Dim tcol As Long Dim tcols As String Dim maxrow_s, maxrow_m, maxcol_m, maxrow_t, maxcol_t As Long Dim dicT1 As Object '項目管理(シート1) Dim dicT2 As Object '項目管理(シート2) Dim dicTw As Object '項目管理 Dim dicN1 As Object '項目管理(シート1) Dim dicN2 As Object '項目管理(シート2) Dim ser_m As String 'ユニークキー列(シート1) Dim ser_t As String 'ユニークキー列(シート2) Dim Key As Variant Dim item As Variant Dim RC As Variant Dim SetFile As Variant Dim newSh As Worksheet Dim mwb As Workbook Dim twb As Workbook Dim tsh As Worksheet Dim maxrow_s2 As Long '追加 回答者 Dim rng As Range '追加 回答者 Set ssh = Worksheets("設定") Set dicT1 = CreateObject("Scripting.Dictionary") ' 連想配列の定義 Set dicT2 = CreateObject("Scripting.Dictionary") ' 連想配列の定義 Set dicTw = CreateObject("Scripting.Dictionary") ' 連想配列の定義 Set dicN1 = CreateObject("Scripting.Dictionary") ' 連想配列の定義 Set dicN2 = CreateObject("Scripting.Dictionary") ' 連想配列の定義 '/////////////////////////////////コモンダイアログから転記先のシートを開く RC = MsgBox("書き込むファイルを選択してください。", vbYesNo + vbQuestion, "確認") If RC = vbNo Then Exit Sub SetFile = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") If SetFile = "False" Then MsgBox "キャンセルしました" Exit Sub End If Set mwb = Workbooks.Open(SetFile) Set msh = mwb.Worksheets("管理表") Worksheets.Add After:=msh Set newSh = ActiveSheet newSh.Name = "差分" Set sbsh = Worksheets("差分") '/////////////////////////////////後で書き換えます◎ Sheets("管理表").Select Rows("1:4").Select Selection.Copy Sheets("差分").Select Rows("1:1").Select ActiveSheet.Paste Application.DisplayAlerts = True Application.CutCopyMode = True '/////////////////////////////////最大行の取得し、設定シートに記載した列を取得 maxrow_s = ssh.Cells(Rows.Count, "G").End(xlUp).Row 'A列の最大行取得 maxrow_s2 = ssh.Cells(Rows.Count, "L").End(xlUp).Row '追加回答者 L列の最大行取得 Set rng = ssh.Range("L2:L" & maxrow_s2) '追加回答者 '設定シート列取得 For wrow = 3 To maxrow_s dicT1(ssh.Cells(wrow, "G").Value) = ssh.Cells(wrow, "H").Value dicT2(ssh.Cells(wrow, "G").Value) = ssh.Cells(wrow, "I").Value Next If dicT1.Exists("ユニークキー") = False Then MsgBox ("設定シートに「ユニークキー」の項目なし") Exit Sub End If ser_m = dicT1("ユニークキー") ser_t = dicT2("ユニークキー") '各項目を記憶(シート1の列指定部分) maxcol_m = msh.Cells(4, Columns.Count).End(xlToLeft).Column '1行目の最終列を求める For wcol = 1 To maxcol_m dicTw(msh.Cells(4, wcol).Value) = wcol Next '項目存在チェック For Each item In dicT1 If dicTw.Exists(item) = False Then MsgBox (item & "は管理表にありません") Exit Sub End If Next '項目の列位置チェック For Each item In dicT1 wcols = dicT1(item) If msh.Cells(4, wcols).Value <> item Then msh.Cells(4, dicTw(item)).Interior.Color = vbYellow MsgBox ("処理中止しました") Exit Sub End If Next maxrow_m = msh.Cells(Rows.Count, ser_m).End(xlUp).Row 'sheet1 ユニークキー列の最大行取得 'ユニークキーの行を記憶 For wrow = 4 To maxrow_m dicN1(msh.Cells(wrow, ser_m).Value) = wrow Next RC = MsgBox("参照するファイルを選択してください。", vbYesNo + vbQuestion, "確認") If RC = vbNo Then Exit Sub SetFile = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") If SetFile = "False" Then MsgBox "キャンセルしました" Exit Sub End If Set twb = Workbooks.Open(SetFile) Set tsh = twb.Worksheets("管理表") '各項目を記憶 maxcol_t = tsh.Cells(3, Columns.Count).End(xlToLeft).Column '1行目の最終列を求める dicTw.RemoveAll For wcol = 1 To maxcol_t dicTw(tsh.Cells(3, wcol).Value) = wcol Next '項目存在チェック For Each item In dicT2 If dicTw.Exists(item) = False Then MsgBox (item & "は管理表にありません") Exit Sub End If Next '項目の列位置チェック For Each item In dicT2 wcols = dicT2(item) If tsh.Cells(3, wcols).Value <> item Then MsgBox (item & "の列が異なります") Exit Sub End If Next maxrow_t = tsh.Cells(Rows.Count, ser_t).End(xlUp).Row 'sheet1 ユニークキー列の最大行取得 'ユニークキーの行を記憶 For wrow = 3 To maxrow_t dicN2(tsh.Cells(wrow, ser_t).Value) = wrow Next 'DE列をクリア msh.Range("DE2:DE" & maxrow_m).Value = "" msh.Range("DE2:DE" & maxrow_m).Interior.Pattern = xlNone '/////////////////////////////////比較⇒処理 For wrow = 4 To maxrow_m Key = msh.Cells(wrow, ser_m).Value 'If msh.Cells(wrow, "B").Value Like "*A*" _ 'Or msh.Cells(wrow, "B").Value Like "*B*" Then If IfInstrs(msh.Cells(wrow, "B").Value, rng) = True Then '追加 回答者 If msh.Cells(wrow, "G").Value = "設計OK" Or msh.Cells(wrow, "G").Value = "検討中" Then If dicN2.Exists(Key) = False Then 'シート1にありシート2にないユニークキーの処理 msh.Cells(wrow, "DE").Value = "レコード削除" msh.Cells(wrow, "DE").Interior.Color = vbYellow Else trow = dicN2(Key) For Each item In dicT1 If item <> "ユニークキー" Then wcols = dicT1(item) tcols = dicT2(item) If msh.Cells(wrow, wcols).Value = "" _ And tsh.Cells(trow, tcols).Value = "" Then Else '転記処理' If msh.Cells(wrow, "B").Value Like "*A*" _ Or msh.Cells(wrow, "B").Value Like "*B*" Then If msh.Cells(wrow, "G").Value = "設計OK" Or msh.Cells(wrow, "G").Value = "検討中" Then If msh.Cells(wrow, wcols).Value = "" Or msh.Cells(wrow, wcols).Value = "-" Then msh.Cells(wrow, wcols).Value = tsh.Cells(trow, tcols).Value msh.Cells(wrow, "DE").Value = "転記あり" msh.Cells(wrow, wcols).Interior.Color = vbCyan msh.Cells(wrow, wcols).Interior.Color = vbCyan Else '元データがあり差分がある場合、DE列に「変更あり」を記載し、黄色く色付け '差分ファイルに転記し色付け If msh.Cells(wrow, "B").Value Like "*A*" _ Or msh.Cells(wrow, "B").Value Like "*B*" Then If msh.Cells(wrow, "G").Value = "設計OK" Or msh.Cells(wrow, "G").Value = "検討中" Then If msh.Cells(wrow, wcols).Value <> tsh.Cells(trow, tcols).Value Then msh.Cells(wrow, "DF").Interior.Color = vbYellow msh.Cells(wrow, "DF") = "変更あり" msh.Cells(wrow, wcols).Interior.Color = vbYellow sbsh.Cells(wrow, wcols).Value = tsh.Cells(trow, tcols).Value sbsh.Cells(wrow, wcols).Interior.Color = vbYellow End If End If End If End If End If End If End If End If Next 'Next dicN2.Remove (Key) End If End If End If Next '/////////////////////////////////ユニークキーが追加されるようなことがある場合、ここをアクティブにする 'シート2にありシート1にないユニークキーを追加する 'wrow = maxrow_m + 1 'For Each key In dicN2 ' trow = dicN2(key) ' For Each item In dicT1 ' wcols = dicT1(item) ' tcols = dicT2(item) ' msh.Cells(wrow, wcols).Value = tsh.Cells(trow, tcols).Value ' Next ' wrow = wrow + 1 'Next '/////////////////////////////////ファイルを閉じて完了 Application.DisplayAlerts = False twb.Close False Application.DisplayAlerts = True MsgBox "完了しました" End Sub Public Function IfInstrs(ByVal word As String, ByVal rng As Range) As Boolean Dim wrg As Range For Each wrg In rng If InStr(1, word, wrg.Value) > 0 Then IfInstrs = True Exit Function End If Next IfInstrs = False End Function
Standard input is empty
Visual Basic.Net Compiler version 0.0.0.5943 (Mono 4.0.1 - tarball) Copyright (C) 2004-2010 Rolf Bjarne Kvinge. All rights reserved. /home/BmUIOc/prog.vb (3,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (4,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (5,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (6,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (7,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (8,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (9,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (10,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (11,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (12,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (13,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (14,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (15,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (16,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (17,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (18,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (19,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (20,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (21,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (22,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (23,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (24,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (25,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (26,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (27,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (28,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (29,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (30,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (31,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (32,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (33,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (34,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (35,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (36,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (43,7) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (45,7) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (46,16) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (47,15) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (48,23) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (49,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (50,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (52,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (53,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (55,15) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (56,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (57,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (59,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (63,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (64,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (65,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (66,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (67,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (68,16) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (70,20) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (71,20) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (76,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (77,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (78,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (80,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (81,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (82,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (83,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (85,7) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (86,15) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (87,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (88,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (90,10) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (91,10) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (94,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (95,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (96,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (97,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (101,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (102,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (103,19) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (104,17) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (105,15) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (106,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (110,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (111,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (112,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (113,16) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (114,19) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (115,17) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (116,15) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (117,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (120,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (122,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (123,14) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (124,9) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (125,7) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (126,7) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (127,12) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (128,7) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (129,15) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (130,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (131,11) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (132,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (133,8) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (137,13) : error VBNC30203: Identifier expected. /home/BmUIOc/prog.vb (138,10) : error VBNC30203: Identifier expected. vbnc : Command line : error VBNC30041: Too many errors.
Standard output is empty