Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


9970 / 13646 ツリー ←次へ | 前へ→

【24479】データ取得 VBA初心者 05/4/22(金) 22:09 質問[未読]
【24480】Re:データ取得 ponpon 05/4/22(金) 22:53 発言[未読]
【24481】Re:データ取得 VBA初心者 05/4/22(金) 23:33 質問[未読]
【24483】Re:データ取得 ichinose 05/4/22(金) 23:45 発言[未読]
【24484】Re:データ取得 訂正 ichinose 05/4/22(金) 23:58 発言[未読]
【24519】Re:データ取得 訂正 ponpon 05/4/23(土) 23:31 質問[未読]
【24521】Re:データ取得 訂正 ichinose 05/4/24(日) 1:02 発言[未読]
【24529】Re:データ取得 訂正 ponpon 05/4/24(日) 14:10 お礼[未読]
【24485】Re:データ取得 ウッシ 05/4/23(土) 0:03 回答[未読]
【24486】Re:データ取得 VBA初心者 05/4/23(土) 0:12 質問[未読]
【24487】Re:データ取得 ichinose 05/4/23(土) 0:23 発言[未読]
【24489】Re:データ取得 VBA初心者 05/4/23(土) 1:00 お礼[未読]
【24492】Re:データ取得 ichinose 05/4/23(土) 9:06 発言[未読]
【24493】Re:データ取得 ウッシ 05/4/23(土) 9:40 発言[未読]

【24479】データ取得
質問  VBA初心者  - 05/4/22(金) 22:09 -

引用なし
パスワード
   こんばんは、ご教示お願いします。
下記のように各会社の最終データのID取得をフォームで
(例ではと東北会社の1035)
表示させたいのですが、どの様にしたらよいでしょうか?
教えていただけますでしょうか?
   A     B     C   D   E
1  会社名  個人ID   名前
2  東北    1010
3  東北    1011
4  東北    1012
5  東北    1013
*   *
*   *
25  東北    1035  ← 会社の最終データのID取得
26  水野    2010
27  水野    2011
28  水野    2012
29  水野    2013

【24480】Re:データ取得
発言  ponpon  - 05/4/22(金) 22:53 -

引用なし
パスワード
   こんばんは。
フォームではありませんが、
sheet1にデータがあるとして、
sheet2に抽出します。

Sub test()
  Dim myRng As Range
  Dim myRng2 As Range
  Dim r As Range
  
  With Worksheets("sheet1")
    Set myRng = Range("A2", Range("A65536").End(xlUp))
    For Each r In myRng
    If r.Value <> r.Offset(1, 0).Value Then
      With Worksheets("sheet2")
       .Range("A1:C1").Value = Worksheets("sheet1").Range("A1:C1").Value
       Set myRng2 = .Range("A65536").End(xlUp).Offset(1, 0)
       myRng2 = r.Value
       myRng2.Offset(0, 1) = r.Offset(0, 1).Value
       myRng2.Offset(0, 2) = r.Offset(0, 2).Value
      End With
    End If
    Next
  End With
End Sub

【24481】Re:データ取得
質問  VBA初心者  - 05/4/22(金) 23:33 -

引用なし
パスワード
   ▼ponpon さん
ありがとうございます。
コンボボックスを使って会社名を選択し、データを取得したいのですが
どうも考えつかなくて・・・
中途半端ですがコード書きました。

Private Sub CommandButton1_Click()
Unload UserForm1
If ComboBox1.ListIndex <> -1 Then
kaisya = ComboBox1.Value
ここからイメージがわかなくて・・・

------------------------------------
Private Sub UserForm_Initialize()
'会社名選択
ComboBox1.List = Worksheets("参照").Range("kaisya").Value
End Sub
-------------------------------------

【24483】Re:データ取得
発言  ichinose  - 05/4/22(金) 23:45 -

引用なし
パスワード
   ▼VBA初心者 さん、ponpon さん、こんばんは。
新規ブックのSheet1という名前のシートに例の
   A     B     C   D   E
1  会社名  個人ID   名前
2  東北    1010
3  東北    1011
4  東北    1012
5  東北    1013
*   *
*   *
25  東北    1035  ← 会社の最終データのID取得
26  水野    2010
27  水野    2011
28  水野    2012
29  水野    2013

のデータが入っていたとします。

Sheet2は、ワーク領域として使用しますから、空けておいてください。


次にユーザーフォーム(Userform1)の構成です。

会社名選択のコンボボックスコントロール(Combobox1)
最終ID表示用ラベルコントロール(ラベル1)
最終ID取得実行ボタン(Commandbutton1)を配置して下さい。

では、コードです。
Userform1のモジュールに
'=================================================================
Private Sub UserForm_Initialize()
  With Label1
   .Font.Size = 12
   .Caption = ""
   .TextAlign = fmTextAlignRight
   .SpecialEffect = fmSpecialEffectSunken
   .BackColor = &HFFFFFF
   End With
  With Worksheets("sheet1")
    Set rng1 = .Range("a1", .Cells(.Rows.Count, 1).End(xlUp))
    With rng1
     .AdvancedFilter xlFilterCopy, , Worksheets("sheet2").Range("a1"), True
     End With
    End With
  With Worksheets("sheet2")
    Set rng2 = .Range("a2", .Cells(.Rows.Count, 1).End(xlUp))
    If rng2.Row > 1 Then
     With ComboBox1
      .List() = rng2.Value
      .Style = fmStyleDropDownList
      .ListIndex = 0
      End With
     .Cells.ClearContents
     End If
    End With
End Sub
'=================================================================
Private Sub CommandButton1_Click()
  Dim rng As Range
  Dim r_ad As String
  With Worksheets("sheet1")
   Set rng = .Range("a2", .Cells(.Rows.Count, 1).End(xlUp))
   If rng.Row > 1 Then
     r_ad = rng.Address(, , , True)
     rr = Evaluate("max(if(" & r_ad & "=""" & ComboBox1.Text & """,row(" & r_ad & ")))")
     Label1.Caption = .Cells(rr, 2).Value
     End If
   End With
End Sub


標準モジュールに

'================================================
Sub main()
  UserForm1.Show
End Sub


これでMainを実行してみて下さい。

コンボボックスで選択後、ボタンをクリックして下さい。
最終IDが表示されるはずなんですが・・・。

あくまでも新規ブックに新たに上記を作成して(もちろん、データも)実行して下さいね!!
後は、体裁の問題だと思います。


>ありがとうございます。
>コンボボックスを使って会社名を選択し、データを取得したいのですが
>どうも考えつかなくて・・・
>中途半端ですがコード書きました。
>
>Private Sub CommandButton1_Click()
>Unload UserForm1
>If ComboBox1.ListIndex <> -1 Then
>kaisya = ComboBox1.Value
>ここからイメージがわかなくて・・・
>
>------------------------------------
>Private Sub UserForm_Initialize()
>'会社名選択
>ComboBox1.List = Worksheets("参照").Range("kaisya").Value
>End Sub
>-------------------------------------

【24484】Re:データ取得 訂正
発言  ichinose  - 05/4/22(金) 23:58 -

引用なし
パスワード
   一箇所訂正です。

>▼VBA初心者 さん、ponpon さん、こんばんは。
>新規ブックのSheet1という名前のシートに例の
>   A     B     C   D   E
>1  会社名  個人ID   名前
>2  東北    1010
>3  東北    1011
>4  東北    1012
>5  東北    1013
>*   *
>*   *
>25  東北    1035  ← 会社の最終データのID取得
>26  水野    2010
>27  水野    2011
>28  水野    2012
>29  水野    2013
>
>のデータが入っていたとします。
>
>Sheet2は、ワーク領域として使用しますから、空けておいてください。
>
>
>次にユーザーフォーム(Userform1)の構成です。
>
>会社名選択のコンボボックスコントロール(Combobox1)
最終ID表示用ラベルコントロール(Label1)←ここ
>最終ID取得実行ボタン(Commandbutton1)を配置して下さい。
>

【24485】Re:データ取得
回答  ウッシ  - 05/4/23(土) 0:03 -

引用なし
パスワード
   こんばんは

「参照」シートの IU、IV列を作業列として使います。

ユーザーフォーム(Userform1)の構成は、

会社名選択のコンボボックスコントロール(Combobox1)
最終ID表示用ラベルコントロール(Label1)
終了用コマンドボタン(Commandbutton1)を配置して下さい。

Private Sub ComboBox1_Change()
  Dim fR As Range
  With Worksheets("参照").Range("A1").CurrentRegion.Columns(1)
    Me.Label1.Caption = ""
    Set fR = .Cells.Find( _
      Me.ComboBox1.Value, .Cells(1), xlFormulas, _
        xlWhole, , xlPrevious)
    If fR Is Nothing Then
      Me.Label1.Caption = "該当会社無し。"
    Else
      Me.Label1.Caption = fR(1, 2).Value
    End If
  End With
End Sub

Private Sub CommandButton1_Click()
  Unload Me
End Sub

Private Sub UserForm_Initialize()
  Dim fD As Variant
  Application.ScreenUpdating = False
  With Worksheets("参照").Range("A1").CurrentRegion.Columns(1)
    .Offset(, 254).Formula = "=A1"
    .Offset(, 255).Formula = "=IF(A1<>A2,B1,"""")"
    With .Offset(, 254).Resize(, 2)
      .Value = .Value
      .Sort .Columns(2), xlAscending, header:=xlYes
    End With
    On Error Resume Next
    fD = .Offset(1, 255).SpecialCells(xlCellTypeConstants) _
        .Offset(, -1).Value
    Me.ComboBox1.List = fD
    On Error GoTo 0
    .Offset(, 254).Resize(, 2).ClearContents
  End With
  Application.ScreenUpdating = True
  Me.Label1.Caption = ""
End Sub

標準モジュールに

'================================================
Sub main()
  UserForm1.Show
End Sub

これでMainを実行してみて下さい。

コンボボックスで選択でラベルにIDを表示します。

一部文章お借りしました。>chinose さん

【24486】Re:データ取得
質問  VBA初心者  - 05/4/23(土) 0:12 -

引用なし
パスワード
   ▼ichinose さん:
こんばんは。
ありがとうございます。すごく勉強になりました。
もうひとつ質問させてください。
会社名,東北で新規登録する場合、ラベルコントロールに自動番号?1036
(最終ID 1035な為)を表示する場合はどの様にしたらよいでしょうか?


>   A     B     C   D   E
>1  会社名  個人ID   名前
>2  東北    1010
>3  東北    1011
>4  東北    1012
>5  東北    1013
>*   *
>*   *
>25  東北    1035  ← 会社の最終データのID取得
>26  水野    2010
>27  水野    2011
>28  水野    2012
>29  水野    2013
>
>会社名選択のコンボボックスコントロール(Combobox1)
>最終ID表示用ラベルコントロール(ラベル1)
>最終ID取得実行ボタン(Commandbutton1)を配置して下さい。
>
>では、コードです。
>Userform1のモジュールに
>'=================================================================
>Private Sub UserForm_Initialize()
>  With Label1
>   .Font.Size = 12
>   .Caption = ""
>   .TextAlign = fmTextAlignRight
>   .SpecialEffect = fmSpecialEffectSunken
>   .BackColor = &HFFFFFF
>   End With
>  With Worksheets("sheet1")
>    Set rng1 = .Range("a1", .Cells(.Rows.Count, 1).End(xlUp))
>    With rng1
>     .AdvancedFilter xlFilterCopy, , Worksheets("sheet2").Range("a1"), True
>     End With
>    End With
>  With Worksheets("sheet2")
>    Set rng2 = .Range("a2", .Cells(.Rows.Count, 1).End(xlUp))
>    If rng2.Row > 1 Then
>     With ComboBox1
>      .List() = rng2.Value
>      .Style = fmStyleDropDownList
>      .ListIndex = 0
>      End With
>     .Cells.ClearContents
>     End If
>    End With
>End Sub
>'=================================================================
>Private Sub CommandButton1_Click()
>  Dim rng As Range
>  Dim r_ad As String
>  With Worksheets("sheet1")
>   Set rng = .Range("a2", .Cells(.Rows.Count, 1).End(xlUp))
>   If rng.Row > 1 Then
>     r_ad = rng.Address(, , , True)
>     rr = Evaluate("max(if(" & r_ad & "=""" & ComboBox1.Text & """,row(" & r_ad & ")))")
>     Label1.Caption = .Cells(rr, 2).Value
>     End If
>   End With
>End Sub
>
>
>標準モジュールに
>
>'================================================
>Sub main()
>  UserForm1.Show
>End Sub
>
>
>これでMainを実行してみて下さい。
>
>コンボボックスで選択後、ボタンをクリックして下さい。
>最終IDが表示されるはずなんですが・・・。
>
>あくまでも新規ブックに新たに上記を作成して(もちろん、データも)実行して下さいね!!
>後は、体裁の問題だと思います。

【24487】Re:データ取得
発言  ichinose  - 05/4/23(土) 0:23 -

引用なし
パスワード
   ▼VBA初心者 さん:
>ありがとうございます。すごく勉強になりました。
>もうひとつ質問させてください。
>会社名,東北で新規登録する場合、ラベルコントロールに自動番号?1036
>(最終ID 1035な為)を表示する場合はどの様にしたらよいでしょうか?
本当は、↑これが目的でした?

>>会社名選択のコンボボックスコントロール(Combobox1)
>>最終ID表示用ラベルコントロール(ラベル1)
>>最終ID取得実行ボタン(Commandbutton1)を配置して下さい。
>>
>>では、コードです。
>>Userform1のモジュールに
>>'=================================================================
>>Private Sub UserForm_Initialize()
>>  With Label1
>>   .Font.Size = 12
>>   .Caption = ""
>>   .TextAlign = fmTextAlignRight
>>   .SpecialEffect = fmSpecialEffectSunken
>>   .BackColor = &HFFFFFF
>>   End With
>>  With Worksheets("sheet1")
>>    Set rng1 = .Range("a1", .Cells(.Rows.Count, 1).End(xlUp))
>>    With rng1
>>     .AdvancedFilter xlFilterCopy, , Worksheets("sheet2").Range("a1"), True
>>     End With
>>    End With
>>  With Worksheets("sheet2")
>>    Set rng2 = .Range("a2", .Cells(.Rows.Count, 1).End(xlUp))
>>    If rng2.Row > 1 Then
>>     With ComboBox1
>>      .List() = rng2.Value
>>      .Style = fmStyleDropDownList
>>      .ListIndex = 0
>>      End With
>>     .Cells.ClearContents
>>     End If
>>    End With
>>End Sub
>>'=================================================================
>>Private Sub CommandButton1_Click()
>>  Dim rng As Range
>>  Dim r_ad As String
>>  With Worksheets("sheet1")
>>   Set rng = .Range("a2", .Cells(.Rows.Count, 1).End(xlUp))
>>   If rng.Row > 1 Then
>>     r_ad = rng.Address(, , , True)
>>     rr = Evaluate("max(if(" & r_ad & "=""" & ComboBox1.Text & """,row(" & r_ad & ")))")
      Label1.Caption = .Cells(rr, 2).Value+1
'変更は、これだけでよいと思います。
>>     End If
>>   End With
>>End Sub
>>


ウッシーさん、こんばんは。

>一部文章お借りしました。>chinose さん

いえいえ、私は、ウッシーさんや皆さんのコードをお借りしてますので・・。
(私のお仕事に・・)

【24489】Re:データ取得
お礼  VBA初心者  - 05/4/23(土) 1:00 -

引用なし
パスワード
   ▼ichinose さん・ウッシさん
こんばんは。
本当に勉強になりました。
また何かわからない事がありましたら、
教えてください。
本当にお世話になりました。

【24492】Re:データ取得
発言  ichinose  - 05/4/23(土) 9:06 -

引用なし
パスワード
   ウッシさん、おはようございます。

>ウッシーさん、こんばんは。
ウッシさん、シーと伸びてました。
大変失礼しました。

【24493】Re:データ取得
発言  ウッシ  - 05/4/23(土) 9:40 -

引用なし
パスワード
   おはようございます、ichinose さん

こちらこそ、失礼しました。

>一部文章お借りしました。>chinose さん
「i」が無かったです。
「愛」が無い訳ではないのでユルシテ m(_ _)m

【24519】Re:データ取得 訂正
質問  ponpon  - 05/4/23(土) 23:31 -

引用なし
パスワード
   ichinoseさん。こんばんは。
もし気がつかれたら、返事をいただけるとうれしいです。
コードを自分なりに理解しましたが、
>rr = Evaluate("max(if(" & r_ad & "=""" & ComboBox1.Text & """,row(" & r_ad & ")))")

ここのところが、わかりません。

多分、sheet1のr_adの値がComboBox1.Textならば、一番大きい行番号をrrという変数に格納しなさい。という意味だと思うのですが、
 Evaluateの使い方と、引数の文字列の記述の仕方が全くわかりません。
解説をお願いします。

 

【24521】Re:データ取得 訂正
発言  ichinose  - 05/4/24(日) 1:02 -

引用なし
パスワード
   ▼ponpon さん、こんばんは。
>もし気がつかれたら、返事をいただけるとうれしいです。
吉田が惜しい負け方をしたので眠れなくて・・・。
興味のない方には、何の事やらでしょうねえ・・・。


>コードを自分なりに理解しましたが、
>>rr = Evaluate("max(if(" & r_ad & "=""" & ComboBox1.Text & """,row(" & r_ad & ")))")
このEvaluateの中の数式は配列数式です。
例えば、VBA初心者さんの例でコンボボックスで「東北」を選択した場合、
Evaluateメソッドの中の数式は、

max(if([svtest.xls]Sheet1!$A$2:$A$10="東北",row([svtest.xls]Sheet1!$A$2:$A$10)))

となっています([・・・]の中は、私がテストしたブック名です)。

これは、「会社名が"東北"だったら、行番号返しなさい、そうでなかったら、False」
という配列をまず作成します。

頭にMAX関数がありますから、その配列の中の最大値を返すという数式です。

Max関数はFALSEは無視されます。

よって、東北の最大行が取得できる という数式です。

尚、Evaluateは、全ての配列数式を意図したように返してくれるわけではありませんので
注意して下さい。

必ず、事前に配列を作成してくれるか否かを確認する必要がありそうです。

私の場合、

1 セルに配列数式をサンプルとして作成して意図した値が表示されるか確認

2 Evaluateを使用して確認。駄目な場合は、配列として認識してくれるように工夫
  (トップレベルのIF関数でA1:A10なんて使用していると成功率が高い)

3 実際に運用

という手順で使いますが・・・。

以前に投稿したものですが、

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=8119;id=excel

↑も読んでみて下さい。


>ここのところが、わかりません。
>
>多分、sheet1のr_adの値がComboBox1.Textならば、一番大きい行番号をrrという変数に格納しなさい。という意味だと思うのですが、
> Evaluateの使い方と、引数の文字列の記述の仕方が全くわかりません。
中身は、セルに記述する数式と一緒ですよ!!

もしかしたら、ダブルコーテーションかな?
文字列としてダブルコーテーションを表現するときは "" と二つ連続しなければ
なりませんが・・・。

蛇足ですが、ここの投稿で[A1].valueとか
[now()]なんて表記を見かけたことがありませんか?
これEvaluateの省略表記です。
Evaluate("a1") やEvaluate("now()")と同値です。

ここの投稿では、Range("a1")と記述するのが面倒なときに、たまに[a1]なんて
記述しますが、実際のコードでは使っていません。

以上です。

【24529】Re:データ取得 訂正
お礼  ponpon  - 05/4/24(日) 14:10 -

引用なし
パスワード
   ichinoseさん
ありがとうございました。
Evaluateメソッドについては、
ヘルプでは、よく意味がわかりませんでしたが、
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=8119;id=excelも併せて読ましていただくと、
>私は、「指定された文字列式を評価した結果を返します」という風に理解しています。
なるほどという感じです。

>max(if([svtest.xls]Sheet1!$A$2:$A$10="東北",row([svtest.xls]Sheet1!$A$2:$A$10)))
一般操作の配列数式についても十分理解していないところが多く、
>Max関数はFALSEは無視されます。
 ということすら知りませんでした。
まだ、まだ勉強不足です。

9970 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free