Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
9968 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【24479】データ取得 ＶＢＡ初心者 05/4/22(金) 22:09 質問[未読]
【24480】Re:データ取得 ponpon 05/4/22(金) 22:53 発言[未読]
【24481】Re:データ取得 ＶＢＡ初心者 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:データ取得 ＶＢＡ初心者 05/4/23(土) 0:12 質問[未読]
【24487】Re:データ取得 ichinose 05/4/23(土) 0:23 発言[未読]
【24489】Re:データ取得 ＶＢＡ初心者 05/4/23(土) 1:00 お礼[未読]
【24492】Re:データ取得 ichinose 05/4/23(土) 9:06 発言[未読]
【24493】Re:データ取得 ウッシ 05/4/23(土) 9:40 発言[未読]
【24479】データ取得
質問  ＶＢＡ初心者  - 05/4/22(金) 22:09 -
引用なし
パスワード
   こんばんは、ご教示お願いします。
下記のように各会社の最終データのＩＤ取得をフォームで
（例ではと東北会社の1035）
表示させたいのですが、どの様にしたらよいでしょうか？
教えていただけますでしょうか？
　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　Ｄ　　　Ｅ
1　　会社名　　個人ＩＤ　　　名前
2　　東北　　　　1010
3　　東北　　　　1011
4　　東北　　　　1012
5　　東北　　　　1013
*　　 *
*　　 *
25　 東北　　　　1035　　←　会社の最終データのＩＤ取得
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:データ取得
質問  ＶＢＡ初心者  - 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 -
引用なし
パスワード
   ▼ＶＢＡ初心者 さん、ponpon さん、こんばんは。
新規ブックのSheet1という名前のシートに例の
　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　Ｄ　　　Ｅ
1　　会社名　　個人ＩＤ　　　名前
2　　東北　　　　1010
3　　東北　　　　1011
4　　東北　　　　1012
5　　東北　　　　1013
*　　 *
*　　 *
25　 東北　　　　1035　　←　会社の最終データのＩＤ取得
26　 水野　　　　2010
27　 水野　　　　2011
28　 水野　　　　2012
29　 水野　　　　2013

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

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


次にユーザーフォーム（Userform1）の構成です。

会社名選択のコンボボックスコントロール（Combobox1）
最終ＩＤ表示用ラベルコントロール（ラベル1）
最終ＩＤ取得実行ボタン（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を実行してみて下さい。

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

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


>ありがとうございます。
>コンボボックスを使って会社名を選択し、データを取得したいのですが
>どうも考えつかなくて・・・
>中途半端ですがｺｰﾄﾞ書きました。
>
>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 -
引用なし
パスワード
   一箇所訂正です。

>▼ＶＢＡ初心者 さん、ponpon さん、こんばんは。
>新規ブックのSheet1という名前のシートに例の
>　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　Ｄ　　　Ｅ
>1　　会社名　　個人ＩＤ　　　名前
>2　　東北　　　　1010
>3　　東北　　　　1011
>4　　東北　　　　1012
>5　　東北　　　　1013
>*　　 *
>*　　 *
>25　 東北　　　　1035　　←　会社の最終データのＩＤ取得
>26　 水野　　　　2010
>27　 水野　　　　2011
>28　 水野　　　　2012
>29　 水野　　　　2013
>
>のデータが入っていたとします。
>
>Sheet2は、ワーク領域として使用しますから、空けておいてください。
>
>
>次にユーザーフォーム（Userform1）の構成です。
>
>会社名選択のコンボボックスコントロール（Combobox1）
最終ＩＤ表示用ラベルコントロール（Label1）←ここ
>最終ＩＤ取得実行ボタン（Commandbutton1）を配置して下さい。
>
【24485】Re:データ取得
回答  ウッシ  - 05/4/23(土) 0:03 -
引用なし
パスワード
   こんばんは

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

ユーザーフォーム（Userform1）の構成は、

会社名選択のコンボボックスコントロール（Combobox1）
最終ＩＤ表示用ラベルコントロール（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:データ取得
質問  ＶＢＡ初心者  - 05/4/23(土) 0:12 -
引用なし
パスワード
   ▼ichinose さん：
こんばんは。
ありがとうございます。すごく勉強になりました。
もうひとつ質問させてください。
会社名,東北で新規登録する場合、ラベルコントロールに自動番号？1036
(最終ID 1035な為)を表示する場合はどの様にしたらよいでしょうか？


>　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　Ｄ　　　Ｅ
>1　　会社名　　個人ＩＤ　　　名前
>2　　東北　　　　1010
>3　　東北　　　　1011
>4　　東北　　　　1012
>5　　東北　　　　1013
>*　　 *
>*　　 *
>25　 東北　　　　1035　　←　会社の最終データのＩＤ取得
>26　 水野　　　　2010
>27　 水野　　　　2011
>28　 水野　　　　2012
>29　 水野　　　　2013
>
>会社名選択のコンボボックスコントロール（Combobox1）
>最終ＩＤ表示用ラベルコントロール（ラベル1）
>最終ＩＤ取得実行ボタン（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を実行してみて下さい。
>
>コンボボックスで選択後、ボタンをクリックして下さい。
>最終ＩＤが表示されるはずなんですが・・・。
>
>あくまでも新規ブックに新たに上記を作成して（もちろん、データも）実行して下さいね！！
>後は、体裁の問題だと思います。
【24487】Re:データ取得
発言  ichinose  - 05/4/23(土) 0:23 -
引用なし
パスワード
   ▼ＶＢＡ初心者 さん：
>ありがとうございます。すごく勉強になりました。
>もうひとつ質問させてください。
>会社名,東北で新規登録する場合、ラベルコントロールに自動番号？1036
>(最終ID 1035な為)を表示する場合はどの様にしたらよいでしょうか？
本当は、↑これが目的でした？

>>会社名選択のコンボボックスコントロール（Combobox1）
>>最終ＩＤ表示用ラベルコントロール（ラベル1）
>>最終ＩＤ取得実行ボタン（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:データ取得
お礼  ＶＢＡ初心者  - 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の中の数式は配列数式です。
例えば、ＶＢＡ初心者さんの例でコンボボックスで「東北」を選択した場合、
Evaluateメソッドの中の数式は、

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

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

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

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

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

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

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

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

私の場合、

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

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

３　実際に運用

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

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

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は無視されます。
　ということすら知りませんでした。
まだ、まだ勉強不足です。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
9968 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free