VBA コンボボックスで選んだものを各テキストボックスに反映

いつもお世話になっています。
Sheet2に色々入力されたものがあります。
コンボボックス21で選んだものが該当する行のものを各テキストボックスやコンボボックスにスピンボタン1を押すたびにその該当する行を表記させたいです。

A４から下方向には　りんご　みかん　梨などをコンボボックス２１
B４から下方向には　日付　2020/12/30　などをコンボボックス２0
C４から下方向には　100円　２００円　300円などをコンボボックス２２
などまだたくさんコンボボックスやテキストボックスなどありそのものにスピンボタン１を変更するたびにその行の該当する行を表記させたいです。
A４りんごならB4　C4・・・を上記の内容のものを表記。A4・A６がりんごで、A5が　みかんならその行は飛ばす。
イメージなのですがソートをかけてパソコンモニター上に見えているものを表記することを想像しているのですが・・・。
間違っていたらすみません。

よろしくお願いします。

質問者からの補足コメント

• 初級者丸出しですが、
  For i = 4 To 1000
  
  If Sheet2.Cells(i, "D").Value = "りんご" And UserForm1.ComboBox21.Value = "りんご" Then
  UserForm1.ComboBox1.Value = Sheet2.Cells(i, "K").Value
  End If
  Next
  とやってみましたが、やはり一つしかでませんでした。

    補足日時：2021/01/01 13:10

• つづき
  　　 .Cells(lastRow, 15) = Label25.Caption
  .Cells(lastRow, 16) = Label31.Caption
  .Cells(lastRow, 17) = TextBox2.Text
  .Cells(lastRow, 19) = ComboBox3.Text & ComboBox12.Text
  .Cells(lastRow, 20) = ComboBox4.Text & ComboBox13.Text
  .Cells(lastRow, 21) = Label14.Caption
  .Cells(lastRow, 22) = Label20.Caption

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/01/01 13:33

• .Cells(lastRow, 23) = Label26.Caption
  .Cells(lastRow, 24) = Label32.Caption
  .Cells(lastRow, 25) = TextBox3.Text
  .Cells(lastRow, 26) = ComboBox5.Text & ComboBox14.Text
  .Cells(lastRow, 28) = Label16.Caption
  .Cells(lastRow, 29) = Label22.Caption
  .Cells(lastRow, 30) = Label27.Caption
  .Cells(lastRow, 31) = Label28.Caption

    補足日時：2021/01/01 13:34

• .Cells(lastRow, 32) = TextBox4.Text
  .Cells(lastRow, 33) = ComboBox6.Text & ComboBox15.Text
  .Cells(lastRow, 35) = Label17.Caption
  .Cells(lastRow, 36) = Label23.Caption
  .Cells(lastRow, 37) = Label28.Caption
  .Cells(lastRow, 38) = Label34.Caption
  .Cells(lastRow, 39) = TextBox5.Text
  .Cells(lastRow, 40) = ComboBox7.Text & ComboBox16.Text

    補足日時：2021/01/01 13:35

• .Cells(lastRow, 42) = Label18.Caption
  .Cells(lastRow, 43) = Label24.Caption
  .Cells(lastRow, 44) = Label29.Caption
  .Cells(lastRow, 45) = Label35.Caption
  .Cells(lastRow, 46) = TextBox6.Text
  .Cells(lastRow, 48) = ComboBox8.Text & ComboBox17.Text
  .Cells(lastRow, 49) = ComboBox10.Text & ComboBox19.Text

    補足日時：2021/01/01 13:36

• .Cells(lastRow, 50) = Label15.Caption
  .Cells(lastRow, 51) = Label21.Caption
  .Cells(lastRow, 52) = Label30.Caption
  .Cells(lastRow, 53) = Label36.Caption
  .Cells(lastRow, 54) = TextBox12.Text

    補足日時：2021/01/01 13:37

No.6 ベストアンサー 回答者： めぐみん_ 回答日時： 2021/01/02 18:30

【VBA学習】ローカル変数とグローバル変数を覚えよう！

https://sashimistudio.site/vba-hensu-global/

この『値を保持し続けるか否か』の使い分けが鍵になると思いますよ。

No.5 回答者： めぐみん_ 回答日時： 2021/01/02 15:28

少なくとも２つのコントロール値を連結して格納したセル値から、逆にどのように振り分けて入れるのかって点が疑問。

あとNo.2ではそのまま回答しちゃいましたけど、どちらかと言えば FindNext は不要ではないかなと。

１行毎にデータを取得するのなら『今現在取得しているセル番地』を保持しておいて引き数にある　After にその番地を入れてそこから検索をかければいいのかも。
無論最初に見つけたセル番地は常に（検索値を変更しない限り）保持し続けるとか。

と頭のお味噌は妄想してますが、寒さで指が～。。。

この回答へのお礼

ありがとうございます。ご指示いただいたものをステップ実行していましたら、転記させたいコンボボックスには一つしか表記されなくて、なぜだろうと思ったら、すべてのものを入力させ最後の一つを表記しているのかなと感じました。
なので、やりたいことと離れてしまうのですが見つかった行をどこかに転記するといいのかなと感じましたが、いらないスペースをとってしまうのでやめました。

お礼日時：2021/01/02 17:04

No.4 回答者： めぐみん_ 回答日時： 2021/01/01 14:57

No.3です。

初級者レベルなので勘違いかもですが・・・

シートのセルにある値を各コントロールに表示したいと質問文からは読み取ってたのですが、それは逆なのでしょうか？
このコード全体を細かくは見ていませんが、明らかにユーザーフォーム側のデータをシートのセルに入れてますよね？
⇒見た感じ最終行＋１へのデータ追加にも見える。

補足などにあるコードと質問文がマッチングしないのも不思議なのですが、一体どちらからどちらへしたいのか？が混乱しそう。
しかもこれだけコントロールオブジェクトを使用されているのなら、コントロール種毎に名前を変える（連番をし易くする）とかも必要じゃないかなって感じはします。
いきなりこれほどの物を作ろうとはソコソコ製作されたご経験はあるのですよね？（私はこれほどの規模は作った事ありませんけど）

この回答へのお礼

混乱させてしまい申し訳ありません。NO.3のご回答に
各TextBoxだけならどの列のデータをどのTextBox（コントロール名含む）の情報があれば可能かもしれませんが
とありましたので、NO3のお礼で書かせていただきました。私の意味わからない回答で申し訳ありません。
実際はCombobox21に表示されているものをスピンボタンを押すたびにシート2のD列から探し、NO3のお礼のような列からCombboxやTextboxに転記したいのです。

お礼日時：2021/01/01 15:30

No.3 回答者： めぐみん_ 回答日時： 2021/01/01 12:18

No.2です。

＞コンボボックス21で選んだものが該当する行のものを各テキストボックスやコンボボックスにスピンボタン1を押すたびにその該当する行を表記させたいです。

各TextBoxだけならどの列のデータをどのTextBox（コントロール名含む）の情報があれば可能かもしれませんが、Comboboxにアイテムとして追加しなきゃいけない手間（不要になった際の削除）の必要性がわかりません。
仮に事前に全ての値を取り込んでいるって事なのでしょうか？
あとはスピンボタンとの連携の必要性もですかね。

と『永遠の初級者レベル』には不明な点があるのと『頭のお味噌が冬眠中』の為、ボケた回答になっていたようです。
決してお味噌は酔ってはいませんよ。飲まないですから。

場合によってはExcelBookへの『Microsoft.ACE.OLEDB.12.0』等による接続でRecordsetを用い必要なデータ群を纏めて抽出してからの方が良いのかな？
パワークエリも機能としてあるようですがなんせ基本『Excel2002』から進歩していないもので使った事ないのです。
なので有効か無効かはちょっと不明。

この回答へのお礼

また初心者レベルと言われると思いますが、コマンドボタン1でテキストボックスとコンボボックスの内容をシート2に表記させています。機械のデータを登録しており、その機械の条件に見あったデータを探し出したいのです。
条件を探し出すのは、
Userform２のコンボボックス１にはUserform１のコンボボックス21
Userform２のコンボボックス２にはUserform１コンボボックス2２から探し出したいです。
Dim lastRow As Long
With Worksheets("Sheet2")
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lastRow, 1) = TextBox7.Text
.Cells(lastRow, 2) = ComboBox20.Text
.Cells(lastRow, 3) = ComboBox22.Text
.Cells(lastRow, 4) = ComboBox21.Text
.Cells(lastRow, 5) = TextBox8.Text
.Cells(lastRow, 6) = TextBox11.Text
.Cells(lastRow, 7) = TextBox9.Text
.Cells(lastRow, 8) = TextBox10.Text
.Cells(lastRow, 9) = TextBox1.Text
.Cells(lastRow, 11) = ComboBox1.Text & ComboBox10.Text
.Cells(lastRow, 12) = ComboBox2.Text & ComboBox11.Text
.Cells(lastRow, 13) = Label13.Caption
.Cells(lastRow, 14) = Label19.Caption

お礼日時：2021/01/01 13:30

No.2 回答者： めぐみん_ 回答日時： 2020/12/31 19:52

No.1です。

未検証で申し訳ないです。

Dim myRange As Range
Dim myObj As Range
Dim keyWord As String
Dim f As String

Set myRange = Sheet2.Range("D:D")
keyWord = Me.ComboBox21.Value
Set myObj = myRange.find(keyWord, LookAt:=xlPart)
f = myObj.Address

If myObj Is Nothing Then
Me.ComboBox1.Value = ""

Exit Sub
End If

Do

Me.ComboBox1.Value = Sheet2.Range("K" & myObj.Row).Value
Set myObj = myRange.FindNext(myObj)
Loop While myObj.Address <> f

ではないかなと思うのですけど自信なし。

この回答へのお礼

ありがとうございます。
ご指示いただいたものですと変化が見られませんでした。

お礼日時：2020/12/31 21:43

No.1 回答者： めぐみん_ 回答日時： 2020/12/31 09:15

＞イメージなのですがソートをかけてパソコンモニター上に見えているものを表記することを想像しているのですが・・・。

正解が何であるのかは質問者さんが求めるもので変わるでしょう。
なので『間違っているだろうか？』と疑問を持つよりまずは試して見られては？
その結果として速度云々は別にしてもきちんと動けばコードの整理方法のアドバイスを、もし動かないのならその原因や別の手段のアドバイスを貰った方が勉強になりますよ。

と『毎回の回答が初級レベル』な回答者は思います。
あと何年位かけられたら中級に行けるかなぁ～？？？

この回答へのお礼

ありがとうございます。ソートと考えていました。
FindNextというものを見つけましたが。
Dim myRange As Range
Dim myObj As Range
Dim keyWord As String

Set myRange = Sheet2.Range("D:D")
keyWord = Me.ComboBox21.Value
Set myObj = myRange.find(keyWord, LookAt:=xlPart)

If myObj Is Nothing Then
Me.ComboBox1.Value = ""

Exit Sub
End If

Dim f As String
Dim myCell As Range
Set myCell = myObj
Do
f = myCell.Row

Me.ComboBox1.Value = Sheet2.Range("K" & f).Value
Set myCell = myRange.FindNext(myCell)
Loop While myCell.Row <> myObj.Row

とりあえず、ComboBox1には一つしか表示されません。
まだまだ初級者ですみません。何が足りない、間違っていますでしょうか？

お礼日時：2020/12/31 17:19