EXCEL VBA 取得したセルの列の最終行

前へ 次へ
このQ&Aのポイント
  • EXCEL VBAを使用して、特定の列の最終行を取得する方法について教えてください。
  • また、日付が今日であるかどうかを判定し、特定の条件を満たすセルをカウントする方法も知りたいです。
  • 現在、セルの全体的な結果が取得されてしまう問題にも直面しており、その解決策を教えていただけると助かります。
回答を見る
  • ベストアンサー

EXCEL VBA 取得したセルの列の最終行

お客さんからいただいたEXCELフォーマットに沿って、集計ツールを作成していますが 下記でつまってしまいました。 Wb.Worksheets("Sheet1").Cells.Find("実施日").Select Sel_Col = Selection.Column Last_Row = Wb.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row　※1 For i = Last_Row To 2 Step -1 If Wb.Worksheets("Sheet1").Cells(Sel_Col, i).Value = Day Then　※2 If Wb.Worksheets("Sheet1").Cells(Sel_Col, i).Offset(-5).Value = "A" Then A_count = A_count + 1 ElseIf Wb.Worksheets("Sheet1").Cells(Sel_Col, i).Offset(-5).Value = "B" Then B_count = B_count + 1 ElseIf Wb.Worksheets("Sheet1").Cells(Sel_Col, i).Offset(-5).Value = "C" Then C_count = C_count + 1 End If Else End If Next i まず※1の箇所ですが、Sheet1の実施日と入力されているセルの列番号を取得して その列の最終行を取得したいのですが、上記作成したものですと入力されている列全部の 中での最終行が取得されてしまいます。 この場合、Sel_Col をどのように使えばよろしいでしょうか？ 次に※2ですが、※1で取得した列の最終行から１つずつ上に上がりながら 日付が今日であれば、そのセルから５つ左のセルのA、B、Cいずれかを カウントするという造りにしたいと思っています。 実行すると1004エラーでアプリケーション定義、オブジェクト定義のエラーと 出てしまいます。 Wbはset Wbとして開いたブックを定義しています。 DayはDay = Dateで今日の日付を取得しています。 独学で無茶苦茶なコードですが、 どなたか詳しい方、ご教示お願いいたします。

質問者が選んだベストアンサー

  • ベストアンサー
  • ushi2015
  • ベストアンサー率51% (241/468)
回答No.1

こんにちは 「実施日」というセルは1つしかないのですね？ ' 不要 Wb.Worksheets("Sheet1").Cells.Find("実施日").Select Sel_Col = Wb.Worksheets("Sheet1").Cells.Find("実施日").Column Last_Row = Wb.Worksheets("Sheet1").Cells(Rows.Count, Sel_Col ).End(xlUp).Row で。 Wb.Worksheets("Sheet1").Cells(Sel_Col, i).Value は、 Wb.Worksheets("Sheet1").Cells(i, Sel_Col, i).Value です。 Cells（行, 列）ですから。 全体的には、ループでなくても関数CountIFだけでも出来そうですね。

garigarisama
質問者

お礼

この問題に関して、解決できました。 ありがとうございました。

garigarisama
質問者

補足

ushi2015様 早速ご回答ありがとうございます。 最終行などを取得するのと代入するのを纏めてするということと Cellを使用する際、行と列を間違えていました。 そこは上手くいきましたが、下記がまだ上手くいきません。 得られたカウント数を下記のようにセルに入力したいのですが Rangeだと上手くいき、Cellだとエラーになってしまいます。 何か違いがあるんでしょうか？ ○ Worksheets("Sheet2").Range("C10") = A_Count × Worksheets("Sheet2").Cell(10, 3) = A_Count また、本来は入力する先のセルも行列を取得して 下記のように指定したいというのが、やりたいことになります。 ThisWorkbook.Worksheets("Sheet2").Cell(Thisbook_A_Row, Thisbook_A_Col) = A_Count お手数ですが、宜しくお願いします。

その他の回答 (2)

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.3

補足での、Cell( )はエクセルシートの場合はｓが必要で　シート.Ｃｅｌｌｓ（ｉ，ｊ）の書き方ですよ。質問ではｓを付けているので凡ミスか。小生もよくミスるよね。 （ＷｏｒｄＶＢＡのテーブルなどやると、Ｃｅｌｌ（ｓなし）もでてきて混乱するが。） ーーー 他の点で Sub test01() Set Rng = Union(Range("A2"), Range("b5"), Range("c7"), Range("d9")) Set x = Rng.Find(Range("a1"))　'<-- MsgBox x.Row '<-- MsgBox x.Column End Sub Ｆｉｎｄメソッドは上記のような書き方もできる

garigarisama
質問者

お礼

ありがとうございます。 凡ミスでした。

  • ushi2015
  • ベストアンサー率51% (241/468)
回答No.2

消し忘れました。 Wb.Worksheets("Sheet1").Cells(i, Sel_Col, i).Value は Wb.Worksheets("Sheet1").Cells(i, Sel_Col).Value で。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • VBAで複数の行のナンバーを取得し、その行の列の値を参照して値を入れる

    下記のことをしたいのですが、調べてもわかりません。 どなたか教えてください。 よろしくお願いします。 内容： エクセルシートのA列の値が11である行ナンバーを取得（複数ある可能性があります）する。 その行のE列（5列目）の値と他のテーブルの値を参照して所定の値をA列の値が11である行のE列（5列目）に返す。 以下のVBAを書いてみましたがうまくいきません。 ----------------------------------------------------------------- Dim ROWCOUNT As Integer Dim row_array() As Variant Dim i As Integer '全行数を取得 ROWCOUNT=Worksheets("sheet1").Range("A1",Range("A65536").End(xlUp)).Count For i = 1 To ROWCOUNT If Cells(i, 1).Value = 11 Then Cells(i,1).Offset(0,5).Value=WorksheetFunction.VLookup(Cells(i, 1).Offset(0, 5).Value, Worksheets("sheet2").Range("A1:H1786"), 3, False) End If Next I

  • Excel VBA; 複数のループ処理

    ↓のようなコードがあります。 Dim i As Long, MaxRow1 As Long, MaxRow2 As Long MaxRow1 = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row MaxRow2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To MaxRow1 Worksheets("Sheet1").Cells(i, 1).Value = i . Next i For i = 1 To MaxRow2 Worksheets("Sheet2").Cells(i, 1).Value = i . Next i これらを最大行の多いシートに合わせて、パフォーマンスを良くしたいと思います。 同様の処理をシート1、シート2で実施しているので纏めて記述したいです。 シート1、シート2に対する処理をサブルーチンにする方法しかありませんか? どなたかお願いします。 If MaxRow1 >= MaxRow2 Then For i = 1 To MaxRow1 Else For i = 1 To MaxRow2 End If Worksheets("Sheet1").Cells(i, 1).Value = i Worksheets("Sheet2").Cells(i, 1).Value = i . Next i

  • ExcelのVBAでシート選択・最終行取得がうまくいかない。

    シートA・Bがあり、シートAの変数markが★だったら、シートBへいき、最終行を取得、ということをしたくて以下のようなコードをかきました。 Worksheets("A").Select Last3 = Cells(6).CurrentRegion.Rows.Count Worksheets("B").Select Last1 = Cells(6).CurrentRegion.Rows.Count For w = 1 To Last1 Worksheets("B").Select Mark = Cells(w, 26) If Mark = "★" Then Sheets("A").Select Last3 = Cells(6).CurrentRegion.Rows.Count MsgBox Last3 End If Next ですが、シートAの最終行が表示されます。 どこがちがうのでしょうか？

  • エクセルVBA の変数を使うべきでしょうか？

    はじめまして。エクセル初心者です。 書籍やサイトで勉強させてもらっていますが、VBAがなかなか難しくてすぐに壁にぶつかってしまいます。少々困ってしまい、詳しい方のアドバイスを頂ければと質問を投稿させていただきました。 どうか宜しくお願い致します。質問ですが、 以下のようなコードで、sheet5のB列の任意のセルをダブルクリックした場合、sheet5のBCD列の同じ行のセル値がsheet1の指定した列に入力されるという処理を作りました。 これで一応目的の動作はするのですが、数が増えると「コンパイルエラー・プロシージャが大きすぎます」というメッセージがでてしまいます。列や行には規則性があるので、もしかしたら変数というものを使ってコードを書き直せばいいのかなと思いネットで調べてみたのですが、今のところさっぱり理解できません。 申し訳ありませんが、分かりやすくご教授いただけないでしょうか。バージョンは2003を使っています。 また、下のコードですと、sheet5のBCDいずれかのセルに空白があった場合、sheet1の列に入力されるときに入力される行がずれてしまいます。今は空白を何かで埋めて対処しているのですが、この問題の解決策も教えて頂けると助かります。どうか宜しくお願い致します。 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, cancel As Boolean) If Target.Address = "$B$2" Then Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B2") Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C2") Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D2") Worksheets("sheet1").Activate cancel = True End If If Target.Address = "$B$3" Then Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B3") Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C3") Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D3") Worksheets("sheet1").Activate cancel = True End If If Target.Address = "$B$4" Then Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B4") Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C4") Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D4") Worksheets("sheet1").Activate cancel = True End If 　　・　　 　　・ 　　・　　 　　・ 　　・　　 　　・ End Sub

  • Excel 　VBAのマクロについて

    以下のようなマクロを作りました。 P4に開始番号P6に終わりの番号があるのですが、P4＝P6、つまり、一つだけのシートを作成する分には問題なく動きます。しかしP4に1、P6に5と範囲を増やすとエラーが出て動きません。どこをなおしたらよろしいでしょうか？ Option Explicit Sub 一括() Dim I As Worksheet Dim SheetName As String Dim Prompt As String Dim Col As Integer Dim Cell As Range Dim Row As Long Dim hani As Long For hani = Range("P4").Value To Range("P6").Value Set I = ActiveSheet SheetName = Cells(hani + 4, "K").Value & "(" & Cells(hani + 4, "B") & ")" Prompt = SheetName & "が存在します。" Sheets("基本シート").Copy After:=Sheets("基本シート") On Error GoTo 100 ActiveSheet.Name = SheetName On Error GoTo 0 Range("X3") = I.Cells(hani + 4, "B") Range("E8") = I.Cells(hani + 4, "C") Range("A13") = I.Cells(hani + 4, "D") For Col = 0 To 8 Step 4 Set Cell = I.Cells(hani + 4, "D").Offset(, Col) If Cell > 0 Then Prompt = "該当する日付がありません。" & Cell.Address On Error GoTo 100 Row = WorksheetFunction.Match(Cell, [A:A], 0) On Error GoTo 0 Cells(Row, "E") = I.Cells(hani + 4, "E").Offset(, Col) Cells(Row, "H") = I.Cells(hani + 4, "F").Offset(, Col) If Col < 8 Then Cells(Row, "Y") = I.Cells(hani + 4, "G").Offset(, Col) End If End If Next Col Next hani End 100 If Err <> 1004 Then Error Err End End If MsgBox Prompt, vbCritical Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Sheets(1).Select End Sub

  • Excel　VBAについて教えて下さい。

    00というブックとテストというブックがあります。 00のデータを並び替え、一部変更し、テストのシート3へ貼付けを行いたいのですが、 うまく作動してくれません。以下に現在のコードを記載しましたので、ご指摘いただければと思います。 やりたいことは １．ブック”00”のシート00にて条件がF列が0、E列がHでソートをかけます。 ２．E列のHをすべて数字の1に変更します。 ３．今度は別の条件、F列が1、E列がH、D列が1でソートをかけます。 ４．E列のHをすべて消去します。 ５．すべて表示させA列からE列までのデータを、テストのシート３へ貼付けをします。 コピー＆ペーストは上手くいくのですが、肝心のソート+文字の変更+消去が出来ていません。 ネットを見ながら書いたコードで、VBAを勉強中です。 よろしくお願いします。 あと、この作業の前にブック00のB列にて昇順のソートをかけるというコードを追加したいのですが、 それについても教えていただけるとうれしいです。 よろしくお願い致します。 Private Sub Worksheet_Activate() Dim wb1 As Workbook Dim wb2 As Workbook Dim i As Long Application.ScreenUpdating = False Set wb1 = ActiveWorkbook Set wb2 = Workbooks.Open("C:\Documents and Settings\デスクトップ\メモ\00.xls", ReadOnly:=True) With wb2.Worksheets("00").Range("A1").CurrentRegion .AutoFilter .AutoFilter Field:=6, Criteria1:="=" & "0", Operator:=xlAnd .AutoFilter Field:=5, Criteria1:="=" & "H", Operator:=xlAnd .AutoFilter x = .Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To x If Cells(i, 5) = "H" Then Cells(i, 5) = Replace(Cells(i, 5), "H", "1") End If Next i With wb2.Worksheets("00").Range("A1").CurrentRegion .AutoFilter .AutoFilter Field:=6, Criteria1:="=" & "0", Operator:=xlAnd .AutoFilter Field:=5, Criteria1:="=" & "H", Operator:=xlAnd .AutoFilter Field:=4, Criteria1:="=" & "1", Operator:=xlAnd .AutoFilter x = .Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To x If Cells(i, 5) = "H" Then Cells(i, 5) = Replace(Cells(i, 5), "H", " ") End If Next i wb2.Sheets("00").Range("B1", .Cells(Rows.Count, 5).End(xlUp)).Copy wb1.Sheets("Sheet3").Range("K3").PasteSpecial wb2.Close False Range("A1").Select Application.ScreenUpdating = True End With End With End Sub

  • Excel VBAについて

    早速ですがExcelVBAについて質問です。 年齢がN列にあるとき、M列に年代を入れたいと思います。（例：19才なら10代、30才なら30代） 以下のように作成しましたが、すべてに20と入ったり正常に動作しないときがあります。 Excelは2003で作成していますが、いずれ2007でも使いたいです。 もっと正確に実行できるコードを教えてください。 ワークシート関数での解決は望んでいません。データ数も多く他の作業もマクロで処理するのでマクロを希望しています。よろしくお願いします。 -------------------------- Sub ByAge() Range("N1").Value = "年代別" Dim i As Long, N As Integer For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, 13).Value >= 60 And Cells(i, 13).Value < 70 Then Cells(i, 14).Value = 60 ElseIf Cells(i, 13).Value >= 50 And Cells(i, 13).Value < 60 Then Cells(i, 14).Value = 50 ElseIf Cells(i, 13).Value >= 50 And Cells(i, 13).Value < 60 Then Cells(i, 14).Value = 40 ElseIf Cells(i, 13).Value >= 30 And Cells(i, 13).Value < 40 Then Cells(i, 14).Value = 30 ElseIf Cells(i, 13).Value >= 20 And Cells(i, 13).Value < 30 Then Cells(i, 14).Value = 20 End If Next i MsgBox "完了！" End Sub --------------------------

  • Activeセルの最終列の値を代入し、セル最終行までコピーするには

    最終列の値の代入をここで教えていただいてできました。 J = Range("IV2").End(xlToLeft).Column For i = J to 1 Step -1 If InStr(Cells(2, i).Value, "単価") > 0 Then Cells(3, i).Value = Cells(3, J).Value End If Next i この、Cells(3, i).Value = Cells(3, J).Value を、 "単価"列の最終行までコピーするには どのような記述をしたらいいでしょうか？ しばらく悩んでいますができません。。。 お力を貸してください。！

  • Excel　VBAについて

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, cancel As Boolean) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.Goto Worksheets("人件費").Range("A1") Worksheets("人件費").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Target.Offset(, -5).Value cancel = True End Sub Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, cancel As Boolean) If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub Application.Goto Worksheets("外注費").Range("A1") Worksheets("外注費").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Target.Offset(, -5).Value cancel = True End Sub 上の指令はFの列をダブルクリックすると人件費のシートが開いてAある値を人件費の新しいセルのAに代入する指令ですが それをG列ダブルクリックで外注費シートに同じようにやろうと思いましたが出来ません。 たぶん根本的に書き方が間違っているのかと思われますが、ご指導のほどお願いします。

  • Excel VBAで文字列の部分一致の文字列を表示

    以前、こちらで頭５文字までの一致で文字列を表示するVBAを教えて頂きました。今回はFINDなどの部分一致での文字列を表示することをしたいのですが、ご教示いただけますと幸いです。 下記はSheet1のA3に文字を5文字以内いれるとSheet２のC列からピックアップしてSheet１のA列に文字列を表示する及びSheet２のB列のデータをSheet１のC列に表示させるVBAです。 Sub Test2() Dim Ws1 As Worksheet, Ws2 As Worksheet Dim SData As String, i As Long, VRet As Variant Dim c As Range, LastRow As Long Set Ws1 = Sheets("Sheet1") Set Ws2 = Sheets("Sheet2") SData = CStr(Ws1.Range("A3").Value) If SData = "" Then Exit Sub i = 5 LastRow = Ws1.Cells(Rows.Count, "A").End(xlUp).Row If LastRow < i Then LastRow = i End If Ws1.Range(Ws1.Cells(i, "A"), Ws1.Cells(LastRow, "C")).ClearContents With Ws2 For Each c In .Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp)) VRet = InStr(1, CStr(c.Value), SData, vbTextCompare) If VRet = 1 And Len(c.Value) >= 10 Then Ws1.Cells(i, "A").Resize(1, 2).Value = c.Resize(1, 2).Value 'とりあえずSheet2のB列のデータ(C列のデータに同期したデータ)をSheet1のC列に Ws1.Cells(i, "C").Value = .Cells(c.Row, "B").Value i = i + 1 End If Next End With End Sub 宜しくお願い致します。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する