別のシートに文字や英字をコピーするVBAや数式

質問

Sheet2の3列目(C3:N3)と17列目(C17:N17)には日付が日付順で配置されています。

B行には1から15までの数字がバラバラ(B4:B13)(B18:B22)にあります。 その数字に対応する列(C4:N13)(C18:N22)には英字や文字が配置されています。
Sheet1の2列目には日付が日付順で配置され(C2:N2)、B行には1から15までの数字が順番に配置(B3:B17)されています。

CommandButtonを押すとSheet2の(C4:N13)(C18:N22)が、Sheet1の(C3:N17)に英字や文字が配置されるVBAや数式はどの様にすれば宜しいでしょうか？ ※添付図のSheet1の様な形です。

この時、Sheet2に入力されている文字の｢｜｣と｢―｣はSheet1では空白になる様にします。

回答No.2

SI299792 · ベストアンサー

C3: =SUBSTITUTE(SUBSTITUTE(VLOOKUP($B3,Sheet2!$B:$N,COLUMN()-1,0),"｜",""),"―","")
右下へコピペ。

お礼

ありがとうございました。
数式でも可能な事にビックリしました。
勉強になりました。

回答No.4

imogasi

#1です。
これは色々な方法があると思います。
（１）関数
何番目の行のデータを持ってくる、という関数式で可能だと思います。
（２）VBAを前提にして、
　　　基データに１列追加して、そこへ希望の並べ順になるキーをVBAで作って、その列をキーとして
並べ替える。
これが昔からの、オフライン・バッチ作業の基本方式でした。
Ａｃｃｅｓｓが使えるなら、ファイル結合の仕組が使えると思うが。
（３）Ｆｉｎｄを使って指定のデータを見つけて、その行のデータを持ってくる。
ただし、Find法はVBAの初級者にはむつかしいので、
データ行数が少なければ、全データ行を順次探索で探しても時間的に耐えられるだろう。
（４）ソート・並べ替えの問題なので、ユーザー設定リストに、今回の望む順のキー列のリストを
つくり、そのユーザー設定リストに基いて、ソートする。
参考
https://support.microsoft.com/ja-jp/office/%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E8%A8%AD%E5%AE%9A%E3%83%AA%E3%82%B9%E3%83%88%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E4%B8%A6%E3%81%B9%E6%9B%BF%E3%81%88%E3%82%8B-cba3d67a-c5cb-406f-9b14-a02205834d72
ユーザー設定リストを使ってデータを並べ替える
　これは（Ａ）ＶＢＡを使う（Ｂ）手作業で行う
の両方可能だと思う。
既にどこかのシートに、希望の並べ順の列データがある場合です。ない場合は、リスト作りは
白紙シートのＡ列に手作業で作るのですが、数十行を超えると疲れるのですが。
　数字だけの列の、ユーザー設定リストへの設定はできない、ようです。
この点を知らず、今回時間を取ってしまった。
この質問の１，２，・・１５は、実は氏名とか、部課名や上位の分類名（文字列）だと思う。
隠したい気もちは判るが、実態がわからないので、推測になる。
シートに１列で望みの順のデータが出来れば、ユーザー設定リストへの登録は、
Sub test02()
'内容が数字だけだと、エラーのようだ
Application.AddCustomList [Sheet2!a2:a16]
End Sub
のような、１行で済む。
ーーー
A列が従業員の名前などの場合ならば、下記の方法も可能である。
例データ　Sheet1　A1:D16
ID 順 4月1日 4月2日
上田 1 14 22
吉田 2 22 14
尾上 3 11 25
古川 4 20 16
大野 5 12 24
佐藤 6 16 20
木村 7 15 21
戸田 8 17 19
中野 9 19 17
津山 10 23 13
近藤 11 13 23
宮田 12 18 18
三原 13 25 11
東 14 21 15
門田 15 24 12
ユーザー設定リストーー＞望む氏名順のリスト（ユニークであること）
今回の仮定で
尾上
大野
佐藤
上田
木村
近藤
戸田
宮田
中野
古川
東
吉田
津山
門田
三原
ーー
ソート後の結果
ID 順 4月1日 4月2日
尾上 3 11 25
大野 5 12 24
佐藤 6 16 20
上田 1 14 22
木村 7 15 21
近藤 11 13 23
戸田 8 17 19
宮田 12 18 18
中野 9 19 17
古川 4 20 16
東 14 21 15
吉田 2 22 14
津山 10 23 13
門田 15 24 12
三原 13 25 11
これは手操作でもできるが、マクロの記録を取れば、VBAコードも作れるだろう。

お礼

この度は長い文章で丁寧に説明して頂きありがとうございました。
VBAは文章に書いて分からない部分を簡潔に説明した方が良さそうなのも分かりました。
またお時間あれば宜しくお願いします。

回答No.3

emaxemax

これでどうでしょう？
Sub TEST20210419()
Dim myV, myW
Dim i As Long, n As Long, j As Long
myW = Sheets("Sheet2").Range("B4:N22").Value
Sheets("Sheet1").Range("C3:N17").ClearContents
myV = Sheets("Sheet1").Range("B3:N17").Value
For i = 1 To 15
For n = 1 To 19
If myV(i, 1) = myW(n, 1) Then
For j = 2 To 13
If myW(n, j) = "｜" Or myW(n, j) = "―" Then
myV(i, j) = Empty
Else
myV(i, j) = myW(n, j)
End If
Next j
Exit For
End If
Next n
Next i
Sheets("Sheet1").Range("B3:N17").Value = myV
End Sub

お礼

この度はVBAの例を記入して頂きありがとうございました。
参考にさせて頂きます。
またお時間あれば宜しくお願いします。

回答No.1

imogasi

VBAや数式でなく、操作でできるよ。
ユーザー設定のリスとして、Sheet2のA列の、並べたい順のリストを作る。
Ａ列番号は、ユニークなので好都合。
その並べ順でデータを並べる（手）操作がある。１５ぐらいなら登録は、手作業でもできるが。
ＶＢＡなら、Ｓｈｅｅｔ２のＡ列のデータをそのまま使う方法がある。
ーー
似たような質問を本日？したよね。
その時ユーザー設定リストを使う、別回答をしようとしたが、締め切ってしまっ後だった。
今日は夜遅いので、明日回答が空いておれば、ＶＢＡでのコードを上げるよ。
ーー
こちらが本番なら、前の質問は、あいまいで、回りくどいな。
本件のことなら、画像もあり、ニーズがいっぺんに判る。
しかし画像ファぼやけて見にくい。
小生なら、この質問は、文章で１行で表現するよ。ただし５行ぐらいのデータ例をあげてね。

お礼

この度は長い文章で丁寧に説明して頂きありがとうございました。
VBAは文章に書いて分からない部分を簡潔に説明した方が良さそうなのも分かりました。
またお時間あれば宜しくお願いします。

補足

画像の件はすいません。
次回にまた躓いて質問した時は見易い画像を貼り付けようと思います。
いかんせん素人なのでVBAは教えて頂くと色々な組み立て方の勉強になるので、もしお時間あれば例を記入して頂くと非常に参考になるので助かります。