EXCEL VBAで指定されたrange内の2番目に小さい値の行数を求める方法
- EXCEL VBAで指定されたrange内から2番目に小さい値を検索し、そのセルの行数を求める方法について教えてください。
- Private Sub test()での構文を以下に示します。Worksheets("sheet1").Range("a1:a4")で指定されたrange内から2番目に小さい値を求め、そのセルの行数を表示する方法です。
- WorksheetFunction.Smallを使用して、指定されたrange内の2番目に小さい値を求め、WorksheetFunction.Findを使用してその値のセルを検索します。その後、行数を取得して表示する方法です。
- ベストアンサー
EXCEL VBAがうまく動きません。
指定された rangeの中から2番目に小さい値を検索し、そのセルの行数を求めようとしていますが、えらーが出ます。いくつか試してみましたがだめでした。 初歩的な質問で恐縮ですが、教えてください。 構文は以下のように書きました。 Private Sub test() Dim s As Double Dim r As Range Dim secondsmall As range Dim smallrow as integer r = Worksheets("sheet1").Range("a1:a4") s = WorksheetFunction.Small(r, 2) secondsmall = WorksheetFunction.Find(what:=s) smallrow = secondsmall.row MsgBox smallrow end sub 宜しくお願いします。
- try2003
- お礼率78% (26/33)
- Visual Basic
- 回答数4
- ありがとう数6
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
Private Sub test() Dim s As Double Dim r As Range Dim secondsmall As Range Dim smallrow As Integer Set r = Worksheets("sheet1").Range("a1:a4") Set secondsmall = r.Find(WorksheetFunction.Small(r, 2)) smallrow = secondsmall.Row MsgBox smallrow End Sub Set コマンドが無いのと、Find の使い方(WorksheetFunction:セル内の文字検索)の誤りですね。
その他の回答 (3)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 ワークシート関数のFind とVBAのFindメソッドとは違います。Findメソッドは便利な部分もありますが、日にちの検索に問題が発生したはずです。その場合は、以下のようMatch関数にします。 Private Sub testR() Dim s As Double Dim r As Range Dim smallrow As Long Set r = Worksheets("sheet1").Range("a1:a4") s = WorksheetFunction.Small(r, 2) On Error Resume Next smallrow = WorksheetFunction.Match(s, r, 0) If smallrow > 0 Then MsgBox r.Cells(1).Row + smallrow - 1 End If End Sub
お礼
あどばいすありがとうございます。 match関数も便利ですね。 ただ、下記の記述が何を意味しているのか、 知識にない私には理解できませんでした。 On Error Resume Next MsgBox r.Cells(1).Row + smallrow - 1
Private Sub CommandButton1_Click() Dim N As Integer Dim R As Range N = InputBox("何番目に小さい値を探しますか?") Set R = Worksheets("Sheet1").Range("A1:A4") MsgBox WorksheetFunction.Small(R, N) End Sub
お礼
アドバイスありがとうございます。 でも、この記述だとその最小値しか求められませんでした。 しかし、hana-hana3さんの記述で解決しました。 どうもありがとうございました。
- bin-chan
- ベストアンサー率33% (1403/4213)
> えらーが出ます。いくつか試してみましたがだめでした。 どんなエラーが表示されたか、を記述してください。 r = Worksheets("sheet1").Range("a1:a4") ここで、シート1が選択された状態で始めてますか? 以下の2行に置き換えてみてください。 Worksheets("sheet1").select set r = Worksheets("sheet1").Range("a1:a4").select としたらどうなりますか?
お礼
range を指定するときはsetが必要なんですね。 初歩的な質問ですみませんでした。
関連するQ&A
- エクセル2019 VBAについて
エクセル2019でVBAを使いコピーペーストの勉強中です、次の箇所(Dim i As integr)で、ユーザー定義型は定義されていませんと指摘されて止まってしまします、Sheet("データ")には3行のデータは入っています、VBA初心者のため、この後が、なかなか前に進めません、どうかよろしくおねがいします。 ********************************** Sub 練習() Dim s1 As Worksheet: Set s1 = Worksheets("請求書") Dim s2 As Worksheet: Set s2 = Worksheets("データ") Dim r1 As Integer: r1 = 18 Dim r2 As Integer: r2 = 2 { Dim i As integr } For i = 0 To 2 s1.Range(s1.Cells(r1 + i, 1), s1.Cells(r1 + i, 3)).Value = s2.Range(s2.Cells(r2 + i, 2), s2.Cells(r2 + i, 4)).Value s1.Cells(r1 + i, 4).Value = s1.sells(r1 + i, 2).Value * s1.Cells(r1 + i, 3).Value Next End Sub ***************************************
- ベストアンサー
- Excel(エクセル)
- VBAのボタンで数式を入れる方法
VBA初心者です。 未特定の行のデータシートの行数を調べて、その行数分特定の列に関数を入れるようなVBAを作成中です。 現在、下記のようなコーディングをしているのですが、セルに入れるものが関数になった時点で分からなくなりました。 Private Sub CommandButton1_Click() Dim rs As Integer rs = Range("D2").End(xlDown).Row Dim Ka As Integer Ka = ' →ここに数式を入れる方法が分かりません! Range(Cells(2, 5), Cells(rw, 5)) = Ka Worksheets("Sheet2").Activate MsgBox ("成功" & rs) End Sub こんな感じなんですが、宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルで、こうやっても反応なしです。
よろしくお願いします。以下のように組んで見ました。 Private Sub Worksheet_Change(ByVal Target As range) Dim clm As Integer Dim row As Integer clm = Target.Column row = Target.row If Worksheets("発注指示").Cells(row, clm) = "不足" Then MsgBox "在庫不足", vbOKOnly, "注意" End If End Sub どうして動かないのでしょう。 本当にわからないので、教えてください。 これで一日つぶれました。
- ベストアンサー
- Visual Basic
- Excel VBA Rangeについて
下記のコードは、あるテキストに掲載されていたコードです。 D5に番号を入力すると、F5に文字が表示されるというようなコードです。 下記コードのRange("顧客コード")とは何を意味するのでしょうか? Worksheets("顧客")の意味は分かりますが、私の知識ではRange(" ")の中に入るのは、A1などしか分かりません。 よろしくお願いします。 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim r As Integer, myRange As Range Set myRange = Worksheets("顧客").Range("顧客コード") With Target '変更されたセルがD5だったら If .Row = 5 And .Column = 4 Then '顧客コードの位置を取得 r = Application.WorksheetFunction _ .Match(Target.Value, myRange, 0) 'セルに顧客名を表示 Range("F5") = Worksheets("顧客").Range("B1").Offset(r - 1).Value End If End With End Sub
- ベストアンサー
- オフィス系ソフト
- VBAでVlookup関数を組もうとしていますがエラーが出ます。VBAに詳しい方、教えてください
VBAでvlookup関数を下のように組みましたが、(1)でエラーが出ます。VBAに詳しい方、教えてください。 Sub VLLOKUPによる表の検索4() Dim mykensakuchi Dim mykensakuhan Dim gyo As Integer (1) mykensakuchi = Worksheets("sheet1").Range("a" & gyo).Value mykensakuhan = Worksheets("sheet2").Range("b2:e9") saikagyo = Worksheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row gyo = 2 For gyo = saikagyo To 1 Step -1 With Application.WorksheetFunction Range("b:gyo").Value = .VLookup(mykensakuchi, mykensakuhan, 2, False) End With Next End Sub
- ベストアンサー
- オフィス系ソフト
- Excel VBA 答えが0になってしまうのですがどうしたらなおりますか?
こんばんはB列に上からいくつか数字が入力されているとします。 そのB列の最下行にSUM関数を入力させるマクロを作成しましたが、 なぜか計算結果が0になってしまいます。 循環になってしまっていたので反復計算にチェックをいれ、再計算させましたが思ったような答えが出ませんでした。 この計算結果が0になってしまう現象はどうやっって回避したらよいのでしょうか。合計欄をC列に変えると正常になりますが、B列に計算式を入力させると0になってしまうのは、どうしてなのでしょうか。 下記のコードが悪いのでしょうか?宜しくお願い致します。 (※通常にExcel2003を使用して、単純にセルどうしの引き算をしていても答えが0になってしまう場合があります。これも同じ循環なのかも知れませんので、回避方法を教えてください。) Sub SUM関数を入力する() Dim r As Integer '最下行 Dim m As Integer '対象列 Dim a As Variant Dim siki As Variant Worksheets(3).Activate r = Worksheets(3).Range("B65536").End(xlUp).Row + 1 Range("B" & Format(r)).Select m = 2 'R1C1方式で選択 a = "R" & 2 & "C" & 2 & ":R" & r & "C" & 2 siki = "=sum(" & a & ")" Range(Cells(r, m), Cells(r, m)).FormulaR1C1 = siki End Sub
- ベストアンサー
- その他([技術者向] コンピューター)
- エクセル関数をVBAでやりたい
IFERROR(INDEX(***,MATCH(***)),"")この式を下記マクロに組み込むことは、可能でしょうか? Sub Macro1() ' Dim line3 As Integer Dim line5 As Integer line5 = 2 '初期値を2行目に設定してます Do While Worksheets("Sheet5").Cells(line5, 1).Value > 0 'sheet5の通し番号をsheet3のH列から検索して、その行数をline3に代入する。 line3 = Worksheets("Sheet3").Range("H:H").Find(what:=Worksheets("Sheet5").Cells(line5, 8)).Row 'A,B列内容のコピー Worksheets("Sheet5").Range("A" & line5, "B" & line5).Copy Worksheets("Sheet3").Cells(line3, 1) 'D~G列内容のコピー Worksheets("Sheet5").Range("D" & line5, "G" & line5).Copy Worksheets("Sheet3").Cells(line3, 4) line5 = line5 + 1 '次の行へ Loop ( http://soudan1.biglobe.ne.jp/qa8921867.html )
- ベストアンサー
- Visual Basic
- Excel VBA 引数が2個のマクロの呼び出し方
ExcelのVBAで、 シート上のボタンがクリックされた時に呼び出す マクロ(プロシージャ)の引数が1個の時は、 コード1のようにできましたが、 引数が2個ある時は、コード2のように記述しても、 ボタンをクリックするとエラーになりますが、 【?】の部分をどのように記述すればよいのでしょうか。 (Windows10,Excel2010) -------------------コード1---------------------------------------- Sub test1() Dim row As Integer Dim wave_file_path As String For row = 1 To 2 wave_file_path = ThisWorkbook.Worksheets("Sheet1").Cells(row, 1).Value Call ボタン作成(row, wave_file_path) Next row End Sub Sub ボタン作成(ByVal row As Integer, ByVal wave_file_path As String) Dim cell_loc As String cell_loc = ThisWorkbook.Worksheets("Sheet1").Cells(row, 3).Address ThisWorkbook.Worksheets("Sheet1").Select With ActiveSheet.Buttons.Add(Range(cell_loc).Left, _ Range(cell_loc).Top, _ Range(cell_loc).Width, _ Range(cell_loc).Height) .name = "ボタン_" & cell_loc .OnAction = "'WAVE_PLAY """ & wave_file_path & "" & "'" .Characters.Text = "再生" End With End Sub Sub WAVE_PLAY(ByVal wave_file_path As String) If Dir(wave_file_path) = "" Then MsgBox wave_file_path & vbCrLf & "がありません。", vbExclamation Exit Sub End If Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close " & wave_file_path End Sub ------------------------------------------------------------------- -------------------コード2---------------------------------------- Sub test2() Dim row As Integer Dim wave_file_path As String For row = 1 To 2 wave_file_path = ThisWorkbook.Worksheets("Sheet1").Cells(row, 1).Value Call ボタン作成(row, wave_file_path) Next row End Sub Sub ボタン作成(ByVal row As Integer, ByVal wave_file_path As String) Dim cell_loc As String cell_loc = ThisWorkbook.Worksheets("Sheet1").Cells(row, 3).Address ThisWorkbook.Worksheets("Sheet1").Select With ActiveSheet.Buttons.Add(Range(cell_loc).Left, _ Range(cell_loc).Top, _ Range(cell_loc).Width, _ Range(cell_loc).Height) .name = "ボタン_" & cell_loc .OnAction = "'WAVE_PLAY """ & wave_file_path & "" & "," & row & "'" <==【?】 .Characters.Text = "再生" End With End Sub Sub WAVE_PLAY(ByVal wave_file_path As String, ByVal row As Integer) If Dir(wave_file_path) = "" Then MsgBox wave_file_path & vbCrLf & "がありません。", vbExclamation Exit Sub End If Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close " & wave_file_path ThisWorkbook.Worksheets("Sheet1").Cells(row, 4).Value = "再生済" End Sub -------------------------------------------------------------------
- 締切済み
- Excel(エクセル)
- Excel VBAで検索(Win2000 Excel2000)
現在、下記のようなコードを書いています。データテーブルの縦と横の検索値を探してその列数と行数を返したいのですが、下記の Sub検索1 と Sub検索2 を1つのSubで実行させるにはどうしたらよいのでしょうか?よろしくお願い致します。 ----------------------------------------- Sub 検索1() Worksheets("Data").Activate Dim x As Integer For x = 3 To 22 If Cells(2, x).Value >= 12 Then MsgBox x Exit Sub End If Next MsgBox "見つかりません" End Sub --------------------------------------------- Sub 検索2() Worksheets("Data").Activate Dim i As Integer For i = 4 To 42 If Cells(i, 2).Value = "A" Then MsgBox i Exit Sub End If Next MsgBox "見つかりません" End Sub
- ベストアンサー
- オフィス系ソフト
- Excel VBA 入力規則
入力規則を利用して、3つのセルを連携させることを考えていますが、 不適合な値を張り付けらられた場合に拒否をする方法があるのでしょうか。 Private Sub Worksheet_Change(ByVal Target As Range) Dim ad As String Dim ma As Range Dim ma2 As Range Dim r As Range Dim r2 As Range Dim r3 As Range Dim r1 As Range Dim m As Long Dim m2 As Long Application.EnableEvents = False If Target = "" Then Range("F7").Validation.Delete Range("F7") = "" If Target.Address(0, 0) = "B7" Then Range("D7").Validation.Delete Range("D7") = "" End If GoTo EXIT_SUB End If With Worksheets("Sheet1") ad = "A4" Set r = .Range(ad) Set ma = r.MergeArea Set r1 = r.Offset(0, 1) m = Application.Match(Range("B7"), .Range(r1, .Cells(r.Row + ma.Count - 1, r1.Column)), 0) Set r2 = .Cells(r.Row + m - 1, r1.Column) Set ma2 = r2.MergeArea If Target.Address(0, 0) = "B7" Then If ma.MergeCells Then setValiS Target.Offset(0, 2), r2 Range("F7").Validation.Delete Target.Offset(0, 2) = "" Target.Offset(0, 4) = "" Else MsgBox "A列が連結されていません。" End If ElseIf Target.Address(0, 0) = "D7" Then Set r3 = r2.Offset(0, 1) m2 = Application.Match(Target, .Range(r3, .Cells(r2.Row + ma2.Count - 1, r3.Column)), 0) setValiS Target.Offset(0, 2), .Cells(r2.Row + m2 - 1, r3.Column) Target.Offset(0, 2) = "" End If End With EXIT_SUB: Application.EnableEvents = True End Sub Sub setVali2() Dim tc As Range Dim c As Range Set tc = Worksheets("登録").Range("D3") Set c = Worksheets("Sheet1").Range("C3") setValiS tc, c End Sub Sub setValiS(tc As Range, c As Range) Dim ss As String Debug.Print tc.Address, c.Address ss = getChildren(c) If ss > "" Then With tc.Validation .Delete .Add Type:=xlValidateList, Formula1:=getChildren(c) End With End If Worksheets("登録").Activate End Sub Function getChildren(c As Range) Dim c1 As Range Dim ss As String Dim s1 As String Worksheets("Sheet1").Activate ss = "" For Each c1 In c.MergeArea s1 = c1.Offset(0, 1) If s1 <> "" Then ss = ss & "," & s1 Next c1 If ss <> "" Then ss = Mid(ss, 2) Else MsgBox "データがありません!" End If getChildren = ss End Function Sub Outline() Dim CheckRow As Long Dim Moji As String Dim TopRow As Long Dim EndRow As Long With ActiveSheet .Range("A2").ClearOutline .Outline.SummaryRow = xlAbove CheckRow0 = .Range("A" & .Rows.Count).End(xlUp).Row CheckRow = CheckRow0 Do If Moji = "" Then Moji = .Cells(CheckRow, 1).Value EndRow = CheckRow ElseIf yy_mm(CDate(.Cells(CheckRow, 1).Value)) = yy_mm(CDate(Moji)) Then TopRow = CheckRow If TopRow = 1 Then .Rows(TopRow + 1 & ":" & EndRow).Rows.Group Exit Do End If Else .Rows(TopRow + 1 & ":" & EndRow).Rows.Group CheckRow = CheckRow + 1 Moji = "" End If CheckRow = CheckRow - 1 Loop Until CheckRow = 1 .Rows(CheckRow + 1 & ":" & EndRow).Rows.Group .Outline.ShowLevels RowLevels:=1 ExecuteExcel4Macro "SHOW.DETAIL(1," & CheckRow0 & ",TRUE)" End With End Sub Function yy_mm(d As Date) yy_mm = Format(d, "yy/mm") End Function
- 締切済み
- その他(プログラミング・開発)
お礼
おかげさまでうまく動きました。 どうもありがとうございました。