- ベストアンサー
select case文について
リストから選択とリストに無い値の入力のリストへの追加をしたく、あちこちの情報をつぎはぎで下記のようにVBAで動かそうとしましたが、うまく動いてくれません。 前(Case A)は動くのですが、あと(Case 2)が機能しません。 それと、3つ以上をSelectCaseで組む場合の方法も合わせてお願いします。 Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Dim LastR As Long Select Case Target.Address(0, 0) Application.EnableEvents = False Case "D1" With Worksheets("Sheet2") LastR = .Range("A36636").End(xlUp).Row Set c = .Range("A1:A" & LastR).Find( _ Target.Value, , xlValues, xlWhole, xlByColumns, xlPrevious, True) If c Is Nothing Then If vbNo = MsgBox("リストに追加しますか?", _ vbYesNo, "追加の確認") Then Application.EnableEvents = True Exit Sub End If .Range("A" & LastR + 1).Value = Target.Value .Range("A1:A" & LastR + 1).Name = "リストA" End If With Target.Validation .Delete .Add Type:=xlValidateList, Formula1:="=リストA" .ShowError = False End With Case "E1" With Worksheets("Sheet2") Set c = .Range("B1:B" & LastR).Find( _ Target.Value, , xlValues, xlWhole, xlByColumns, xlPrevious, True) If c Is Nothing Then If vbNo = MsgBox("リストに追加しますか?", _ vbYesNo, "追加の確認") Then Application.EnableEvents = True Exit Sub End If .Range("B" & LastR + 1).Value = Target.Value .Range("B1:B" & LastR + 1).Name = "リストB" End If End Select End With With Target.Validation .Delete .Add Type:=xlValidateList, Formula1:="=リストB" .ShowError = False End With Application.EnableEvents = True End Sub
- fish_001
- お礼率44% (4/9)
- Visual Basic
- 回答数2
- ありがとう数0
- みんなの回答 (2)
- 専門家の回答
質問者が選んだベストアンサー
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Dim LastR As Long Application.EnableEvents = False Select Case Target.Address(0, 0) Case "D1" With Worksheets("Sheet2") LastR = .Range("A36636").End(xlUp).Row Set c = .Range("A1:A" & LastR).Find( _ Target.Value, , xlValues, xlWhole, xlByColumns, xlPrevious, True) If c Is Nothing Then If vbNo = MsgBox("リストに追加しますか?", vbYesNo, "追加の確認") Then Application.EnableEvents = True Exit Sub End If .Range("A" & LastR + 1).Value = Target.Value .Range("A1:A" & LastR + 1).Name = "リストA" End If End With With Target.Validation .Delete .Add Type:=xlValidateList, Formula1:="=リストA" .ShowError = False End With Case "E1" With Worksheets("Sheet2") Set c = .Range("B1:B" & LastR).Find( _ Target.Value, , xlValues, xlWhole, xlByColumns, xlPrevious, True) If c Is Nothing Then If vbNo = MsgBox("リストに追加しますか?", vbYesNo, "追加の確認") Then Application.EnableEvents = True Exit Sub End If .Range("B" & LastR + 1).Value = Target.Value .Range("B1:B" & LastR + 1).Name = "リストB" End If End With With Target.Validation .Delete .Add Type:=xlValidateList, Formula1:="=リストB" .ShowError = False End With End Select Application.EnableEvents = True End Sub が正しいかと。 Select Case 条件対象 Case 条件1 条件1の処理 Case 条件2 条件2の処理 ・ ・ Case 条件n 条件nの処理 Case Else それ以外の処理 End Select 条件に複数該当する場合、上に書かれているほうが適用されます。 あと、Withはネストできなかったと思います。 With A .hogehoge With B .hogehoge End With End With こういう書き方はNG。
その他の回答 (1)
- BellBell
- ベストアンサー率54% (327/598)
とりあえずの問題点は、『With Worksheets("Sheet2")』がSelect Case 内に書かれていて、『End With』が外に書かれているところですね。 Case 分岐の最後にEnd With で括るか、この場合であればSelect Case 前に『With Worksheets("Sheet2")』を書く(それぞれのCase内の『With Worksheets("Sheet2")』は削除)ことですね。 Withは条件分岐(IfやSelect)の中と外、でWith ~ End Withはできません。 中と中、外と外であれば問題なし。 ちなみに、Withはネストできますよ。<#1
関連するQ&A
- 2つのVBAを組み合わせる方法
お世話になります、2つのVBAを組み合わせる方法で迷っています。 1つ目が Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, k As Long, myNum As Long If Intersect(Target, Range("C1,B9:B39")) Is Nothing Or Target.Count > 1 Then Exit Sub Application.EnableEvents = False With Target If .Column = 3 Then myNum = WorksheetFunction.Max(Range("B9:B39")) If IsDate(.Value) Then For i = 9 To 39 If Cells(i, "A").Value = "" Then Cells(i, "B").Value = "" Else Cells(i, "B") = myNum + i - 8 End If Next i End If Else i = .Row If .Value = "" Then Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents Else For k = i + 1 To 39 If Cells(k, "A").Value = "" Then Cells(k, "B").Value = "" Else Cells(k, "B") = Cells(k - 1, "B") + 1 End If Next k End If End If End With Application.EnableEvents = True End Sub です。 2つめが Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True If Intersect(Target, Range("R8:R38")) Is Nothing Then Exit Sub Application.EnableEvents = False Range(Cells(Target.Row, 18), Cells(39, 18)).Value = Target.Value Application.EnableEvents = True End Sub です。2つのPrivate Sub Worksheet_Change(ByVal Target As Range)イベントのVBAですが、どのようにして組み合わせれば良いのでしょうか?
- ベストアンサー
- Excel(エクセル)
- このマクロあっていますでしょうか?よろしくお願いいたします。
★sheetA Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$C$14" And Target.Address <> "$C$19" _ And Target.Address <> "$F$19" Then Exit Sub If Target.Address <> "$R$14" And Target.Address <> "$S$14" _ And Target.Address <> "$T$19" Then Exit Sub Application.EnableEvents = False With Sheets("B") .Range("F14").Value = Range("C14").Value .Range("F17").Value = Range("C19").Value .Range("F20").Value = Range("F14").Value .Range("F23").Value = Range("F19").Value End With With Sheets("C") .Range("F13").Value = Range("R14").Value .Range("F14").Value = Range("S14").Value .Range("F18").Value = Range("T19").Value End With Application.EnableEvents = True End Sub ★sheetB Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$F$14" And Target.Address <> "$F$17" _ And Target.Address <> "$F$23" Then Exit Sub Application.EnableEvents = False With Sheets("A") .Range("C14").Value = Range("F14").Value .Range("C19").Value = Range("F17").Value .Range("F19").Value = Range("F23").Value End With Application.EnableEvents = True End Sub ★sheetC Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$F$13" And Target.Address <> "$F$14" _ And Target.Address <> "$F$18" Then Exit Sub Application.EnableEvents = False With Sheets("A") .Range("R14").Value = Range("F13").Value .Range("S14").Value = Range("F14").Value .Range("T19").Value = Range("F18").Value End With Application.EnableEvents = True End Sub
- ベストアンサー
- オフィス系ソフト
- エクセルのマクロコードについて
お世話になります。 下記コードで、セルごとにクリアをすると、エラーなくうごくのですが、セルをまとめてセルを消すと実行時エラー13型が一致しません。とでてIf Target.Value = "" Thenがだめだよとでてしまいます。 どなたか、回避の方法をご教授ください。 宜しくお願いいたします。 Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B2:E2,G2:J2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub x = Target.Value With Application .ScreenUpdating = False .EnableEvents = False .Undo 'Range("B2").Value = x + Z Z = Target.Offset(1, 0).Value y = Target.Value Target.Value = x + y .EnableEvents = True .ScreenUpdating = True End With Target.Offset(1, 0).Value = x + Z End Sub
- ベストアンサー
- Excel(エクセル)
- excel2007 VBで
下記のマクロ作成して実際にセルにA、あるいは何かデータを入力しても下記イベント?が発生している気配がありません。 Application.EnableEvents = Falseの行がなにか問題なのでしょうか。実プログラムは If Target.Value = "A" Or Target.Value = "A" Thenの他にB、C、計算も含んでいるのですがApplication.EnableEvents = TrueはEnd Subの前行に入れてあります。 他に設定することがあるのでしょうか。どなたか教えてください。 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub '複数セルの入力は無視 Application.EnableEvents = False '割込み停止 ’[B3] = 123 ’Stop If Target.Value = "A" Or Target.Value = "A" Then Target.Value = "A" End If Application.EnableEvents = True '割込み再開 End Sub excel2007 VB6.5です。
- ベストアンサー
- その他MS Office製品
- 連続データのVBAの質問
お世話になります。 Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, k As Long, myNum As Long If Range("C1") <= Date - Day(Date) Then Exit Sub If Not Intersect(Target, Range("C1,B9:B39")) Is Nothing And Target.Count = 1 Then Application.EnableEvents = False With Target If .Column = 3 Then myNum = WorksheetFunction.Max(Range("B9:B39")) If IsDate(.Value) Then For i = 9 To 39 If Cells(i, "A").Value = "" Then Cells(i, "B").Value = "" Else Cells(i, "B") = myNum + i - 8 End If Next i End If Else i = .Row If .Value = "" Then Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents Else For k = i + 1 To 39 If Cells(k, "A").Value = "" Then Cells(k, "B").Value = "" Else Cells(k, "B") = Cells(k - 1, "B") + 1 End If Next k End If End If End With Application.EnableEvents = True ElseIf Not Intersect(Target, Range("R8:R38")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, 18), Cells(39, 18)).Value = Target.Value Application.EnableEvents = True End If End Sub これはセルC1に年月を表記させ、そのC1セルの年月を変更した場合B9~B39のセルが自動で連続データの数字を記入し、B9~B39のどこかのセルを空白にすると、そのセル以降も空白になります。 そこで質問ですが、例えばB14の数字を消すとB15~39まで空白のセルになります。そして、B20に1と入力するとB21~39に連続データの数字が自動記入されます。 そしてC1の年月を次の月に更新した際は、B39で示された数字以降の連続データをB9に表示させるには、どうすれば良いでしょうか?
- ベストアンサー
- Excel(エクセル)
- エクセルのマクロコードに付いて教えて下さい。
下記のマクロコードがありますが、 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$A$1" Then Exit Sub x = Target.Value With Application .ScreenUpdating = False .EnableEvents = False .Undo y = Target.Value Target.Value = x + y .EnableEvents = True .ScreenUpdating = True End With End Sub セル位置の指定を変更する場合は、どの様に 書けば良いのですか? このコードですと、セルA1の入力指定でなっていますが A1~A5までとか。A1、B1,C1とかにする場合はどの様に 書けば良いか教えて下さい。 マクロに付いて、殆ど知識が無いものですので 出来れば、分かり易い説明でお願いします。 宜しくお願いします。
- 締切済み
- 数学・算数
- エクセル VBAマクロ if文 はどうすれば?
先ほど質問したのですが、さらにわからなくなったのでお願いします 先ほどの質問 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ http://okwave.jp/qa/q7236338.html >変数と式の両立は難しいでしょうからどうすればよいのでしょう? ⇒関数では出来無いのはエクセルの常識ですのでマクロ(VBA)組込みになります。 一例です。 対象シートタブ上で右クリック→コードの表示→以下のコード貼り付けてA1に枚数を入力して お試しください。 サンプルコード Private Sub Worksheet_Change(ByVal Target As Range) 単価 = 5 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) And Target.Value > 0 Then Application.EnableEvents = False Target.Value = Target.Value * 単価 Application.EnableEvents = True End If End Sub ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ↓変更&応用したのですが、、、、 EX:(セル) セル番号 用紙種類1~3 用紙種類 C12 A3モノクロ1 D12 A3カラー1 C13 A3モノクロ2 D13 A3カラー2 C14 A3モノクロ3 D14 A3カラー3 ※金額の違いは、モノクロとカラーの値段が違うだけ 1~3は金額的な違いはない とあった場合、 ためしに先ほどのを応用して用いたのですが 変更点は、用紙サイズ、カラーの有無による金額 出力先セルの番号 Private Sub Worksheet_Change(ByVal Target As Range) を Private Sub A4_mono_1(ByVal Target As Range) Private Sub A4_mono_2(ByVal Target As Range) ・ ・ ・ と変更したのですがうまく動作しなかったのですが、 どういった点が悪かったのでしょうか? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 最終的な質問↓ 上記のものはVBAマクロ文は基本1つのみなので、if文で作らなくてはいけないということが分かったのですが、そこでさらに疑問が浮かびました、 Private Sub Worksheet_Change(ByVal Target As Range) If or(target.columns = C12:C14) Then 単価1 = 7.6 If Intersect(Target, Range("C12:C14")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) And Target.Value > 0 Then Application.EnableEvents = False Target.Value = Target.Value * 単価1 Application.EnableEvents = True単価1 = 7.6 elseif or(target.columns = D12:D14) Then 単価2 = 30.6 If Intersect(Target, Range("D12:D14")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) And Target.Value > 0 Then Application.EnableEvents = False Target.Value = Target.Value * 単価2 Application.EnableEvents = True End If End Sub としたっ場合全く式になっていません どのようにすればよいのでしょう?
- ベストアンサー
- オフィス系ソフト
- 入力規則のドロップダウンリストを連動
以下のサイトを参考に別ブックからデータを参照する方法で苦戦しています。 http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_validation.html Sub name_1() Dim lCol As Long, lRow As Long Dim i As Long, nName As String Dim Wb As Workbook ←追記 Set Wb = Workbooks("MyBook.xls") ←追記 On Error Resume Next With Wb.Sheets("Sheet2") lCol = .Range("A1").End(xlToRight).Column ActiveWorkbook.Names("項目リスト").Delete ActiveWorkbook.Names.Add Name:="項目リスト", _ RefersTo:=.Range(.Cells(1, 1), .Cells(1, lCol)) '----名前の定義 For i = 1 To lCol lRow = .Cells(1, i).End(xlDown).Row nName = .Cells(1, i).Value ActiveWorkbook.Names(nName).Delete .Range(.Cells(1, i), .Cells(lRow, i)).CreateNames Top:=True Next i End With End Sub Sub Macro2() name_1 With Range("A2:A10").Validation '--入力規則を削除 .Delete '--入力規則を設定 .Add Type:=xlValidateList, _ Formula1:="=項目リスト" End With '--B2セルへ入力規則を設定 With Range("B2:B10").Validation .Delete .Add Type:=xlValidateList, _ Formula1:="=IF(A2="""",A2,INDIRECT(A2))" End With End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Dim Wb As Workbook ←追記 Set Wb = Workbooks("MyBook.xls") ←追記 If Not (Application.Intersect(Target, Range("A2:B10")) Is Nothing) Then name_1 Application.EnableEvents = False If Target.Column = 1 Then If Target.Value = "" Then Target.Offset(0, 1).Value = "" Else Set c = Wb.Sheets("Sheet2").Range(Target.Value).Find(Target.Offset(0, 1).Value, lookat:=xlWhole) ←ここでエラー If c Is Nothing Then Target.Offset(0, 1).Value = "" End If End If End If If Target.Column = 2 Then If Target.Value = "" Then Target.Offset(0, -1).Value = "" End If End If Application.EnableEvents = True End If End Sub どのように改変すれば良いのでしょうか?
- ベストアンサー
- Visual Basic
- VBA 範囲選択時エラー
Private Sub Worksheet_SelectionChangeのVBAでA列B列C列でワンクリックで文字が入力できるように設定致しました。 その後、A列からC列を範囲選択してDeleteするとデバック 「実行時エラー 型が一致しません」と出てしまいます。業務上、そのセルのデータは一気に消したいので困っております。どなたか分かる方よろしくお願い致します。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range, rng_1 As Range, rng_2 As Range Application.EnableEvents = False Set rng_1 = Range("H17:H100") Set rng_2 = Range("I17:I100") Set rng_3 = Range("J17:J100") Set rng_4 = Range("K17:K100") Application.EnableEvents = True Set rng = Intersect(Target, rng_1) If Not rng Is Nothing Then Cancel = True If Target.Value = "" Then Target.Value = "(1)" Else Target.Value = "(1)" End If Else Set rng = Intersect(Target, rng_2) If Not rng Is Nothing Then Cancel = True If Target.Value = "(2)" Then Target.Value = Empty Else Target.Value = "(2)" End If Else Set rng = Intersect(Target, rng_3) If Not rng Is Nothing Then Cancel = True If Target.Value = "(3)" Then Target.Value = Empty Else Target.Value = "(3)" End If Else Set rng = Intersect(Target, rng_4) If Not rng Is Nothing Then Cancel = True If Target.Value = "(4)" Then Target.Value = Empty Else Target.Value = "(4)" End If End If End If End If End If End Sub
- 締切済み
- Visual Basic
- 連続データのVBAの質問
お世話になります。 Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, k As Long, myNum As Long If Not Intersect(Target, Range("C1,B9:B39")) Is Nothing And Target.Count = 1 Then Application.EnableEvents = False With Target If .Column = 3 Then myNum = WorksheetFunction.Max(Range("B9:B39")) If IsDate(.Value) Then For i = 9 To 39 If Cells(i, "A").Value = "" Then Cells(i, "B").Value = "" Else Cells(i, "B") = myNum + i - 8 End If Next i End If Else i = .Row If .Value = "" Then Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents Else For k = i + 1 To 39 If Cells(k, "A").Value = "" Then Cells(k, "B").Value = "" Else Cells(k, "B") = Cells(k - 1, "B") + 1 End If Next k End If End If End With Application.EnableEvents = True ElseIf Not Intersect(Target, Range("R8:R38")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, 18), Cells(39, 18)).Value = Target.Value Application.EnableEvents = True End If End Sub これはセルC1に年月を表記させ、そのC1セルの年月を変更した場合B9~B39のセルが自動で連続データの数字を記入し、B9~B39のどこかのセルを空白にすると、そのセル以降も空白になります。 そしてR8~R38は、指定範囲のセルに数字を入力したら、そのセル以降の指定した範囲のセルに同じ数字を自動入力するVBAです。 そこで質問ですが、質問した現在は2013年12月ですが、日本時間の現在の年月以前の年月(今で言うと2013年11月以前)をC1に記入した場合はB9~B39の連続データの数字が切り替わらない様にするには、どうすれば宜しいでしょうか?
- ベストアンサー
- Excel(エクセル)