Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
飛び石のセルのリンク
投稿日時: 20/06/23 10:13:40
投稿者: 案山子

Excel VBAで、飛び石で記述されている値を別シートにリンクとして記入したいです。
一応マクロの記録で取り敢えずテストコードを作って汎用型にしようとしましたが、上手くいきません。
 
したいことは2つ。
・飛び石の値を別シートにリンクとして転記。
・貼り付け先は、行の間が空いていない状態である。
 
<イメージ>
商品A 単位
商品説明←この分をコピー
商品B 単位
商品説明
商品C 単位
商品説明

商品A 単位 商品説明
商品B 単位 商品説明
商品C 単位 商品説明
 
このようなデータが500前後あります。
この量を早く処理するために必要なのです。
 
よろしくお願いいたします。

回答
投稿日時: 20/06/23 13:13:08
投稿者: QooApp

初めまして。
今後継続して作業しないならば、オートフィルターを使って指定範囲選択・コピペのほうが早いですよ。
 
手順
・空の別シートを作っておく
・一番左列に1列挿入
・2行で1セットならば、




と挿入したセルに交互に値を入れる(1=商品A 単位の行)
 
エクセルの上部分にデータという項目があると思います。
その中にフィルターというボタンがあります。
・挿入した列とデータの入っている列(子の場合3列ですね)を列すべて選択
・フィルターボタンを押す
・フィルターで1のみ表示にします。

・表示されたデータを 「Ctrl」 「A」 で全選択します。
・選択状態で 「alt」 「;」をおします。(こうするとフィルターで表示部分のみ選択します。
・作っておいた別シートへコピペ
 
フィルターで2のみ表示
○の部分から繰り返して、貼りたいとなりの列へペースト
 
おわり。
 
反復的に何十回も仕事するならばおすすめしませんが、1回きりならばマクロを組まない方が早いです。

回答
投稿日時: 20/06/23 15:24:47
投稿者: mattuwan44

>飛び石で記述されている値を別シートにリンクとして記入したいです。

単に値が必要な時にコピペしてくればいいのか、
参照式をセットしたいのかわかりませんが、
位置に規則性がありそうなので、
 
=OFFSET(Sheet1!$A$3,2*(ROW()-1),0)
 
みたいな式でセルを参照したらいいのではないかと思います。
あとは、フィルハンドルをドラッグして必要なセル範囲にコピーしたらいいかなと思いました。
 
位置関係は微調整願います。
 
あくまでもVBAの勉強だからVBAでって思ってますか?
そうならば、結果として値だけ移せばいいのか、
数式を自動でセットしたいのかを説明しましょう。
 
あと、位置関係も、漠然としたイメージではなく、どこの位置にどんな値があるのか、
セルのアドレス、行番号や列番号がわかるように(空白があれば空白も含む)説明されると
回答が付きやすいと思います。

回答
投稿日時: 20/06/23 22:47:12
投稿者: WinArrow
投稿者のウェブサイトに移動

まず、規則性を整理することです。
  
規則性は、元データの行番号と、数式を入力する側の行番号の関係を整理します。
  
数式を入れる側の行番号(A) 元データの行番号(B)
 1             1
 2 3
 3 5
   
この関係は
A*2-1でBが求められます
  
例.シート2の
 セルA1に
=INDIRECT("Sheet1!A"&ROW()*2-1))
セルB1に
=INDIRECT("Sheet1!B"&ROW()*2-1))
セルC1に
=INDIRECT("Sheet1!A"&ROW()*2))
   
あとは下へ」フィルドラッグで完成します。
INDIRECT関数は、レスポンスが悪いので「値」複写しておきます。
※VBAでなくてもできますが、
 応用すればVBAも可。
  
   
  

回答
投稿日時: 20/06/24 10:57:39
投稿者: WinArrow
投稿者のウェブサイトに移動

参考コードを掲示します。
  
シートのレイアウト等の説明がないので、
 推測して作成してあります。
  
複写元シート:Sheet1
データ表:列はA〜B、行は1行目より
複写先シート:Sheet2
データ表:列はA〜C、行は1行目より
 
※質問タイトルが「リンク」となっているので
数式を代入する方が、質問に対する回答になりますが、
参考コードは「値」代入にしています。
 
実情に合わせて変更してください。
  
Sub Sample()
 Dim RX1 As Long, Rx2 As Long, Rx2Max As Long
 Dim sht1 As Worksheet, sht2 As Worksheet
   
     Set sht1 = Worksheets("Sheet1")
     Set sht2 = Worksheets("Sheet2")
       
     Rx2Max = sht1.Range("A" & sht1.Rows.Count).End(xlUp).Row / 2
           
     With sht2
         For Rx2 = 1 To Rx2Max
             .Cells(Rx2, "A").Value = sht1.Cells(Rx2 * 2 - 1, "A").Value
             .Cells(Rx2, "B").Value = sht1.Cells(Rx2 * 2 - 1, "B").Value
             .Cells(Rx2, "C").Value = sht1.Cells(Rx2 * 2, "A").Value
         Next
     End With
   
 End Sub

投稿日時: 20/06/25 09:32:16
投稿者: 案山子

皆様、ご回答ありがとうございます。
 
色々と説明が足りず申し訳ありません。
状態を説明させていただきます。
・今後継続して作業するためにVBAを使用したいです。
・Sheet2に記述されている項目・数値をSheet1の指定された場所に表示したく、参照式を自動セットしたいです。
・必要な列の項目をリンクとして引き込むものを変更して、使用してみようとここ見ましたがうまく行きませんでした。

Sub リンク書き込み2()
    Dim myRng As Range
    '↓書き込み先の指定値'
    Set myRng = Worksheets("Sheet2").Range("C1:C762,G1:G762")
    '↓読み込み先の指定値'
    myRng.FormulaR1C1 = "=Sheet1!RC"
End Sub

シートのイメージは、最初の書き込みとかぶりますが、2行で表示している項目を1行に変換して別シートに、
参照式を自動セットしたいです。
 
<イメージ>
商品A 単位
商品説明←この分をコピー
商品B 単位
商品説明
商品C 単位
商品説明

商品A 単位 商品説明
商品B 単位 商品説明
商品C 単位 商品説明
 
まだ、上手く説明できていないかもしれませんが、なにとぞよろしくお願いいたします。

回答
投稿日時: 20/06/25 18:25:13
投稿者: WinArrow
投稿者のウェブサイトに移動

回答者には、あなたのPCの画面は見えません。
列番号、行番号を使って、表のレイアウトを説明しましょう。
 
 
 

回答
投稿日時: 20/06/25 20:15:53
投稿者: WinArrow
投稿者のウェブサイトに移動

表のレイアウトは前レスと同じです。
数式代入案です。
 
Sub Sample()
Dim Rx As Long, RxMax As Long
    With Sheets("Sheet1")
        RxMax = .Range("A" & .Rows.Count).End(xlUp).Row / 2
    End With
 
    With Sheets("Sheet2")
        For Rx = 1 To RxMax
            With .Cells(Rx, "A")
                .FormulaR1C1 = "=SHEET1!R" & .Row * 2 - 1 & "C"
                .Offset(, 1).FormulaR1C1 = "=SHEET1!R" & .Row * 2 - 1 & "C2"
                .Offset(, 2).FormulaR1C1 = "=SHEET1!R" & .Row * 2 & "C[-2]"
            End With
        Next
    End With
End Sub
 
 

投稿日時: 20/06/26 15:28:03
投稿者: 案山子

WinArrow さんの引用:
回答者には、あなたのPCの画面は見えません。
列番号、行番号を使って、表のレイアウトを説明しましょう。

 
大変失礼いたしました。
<イメージ>
Sheet2
  C    D
6 商品名 単位 
7 商品説明
8 商品名 単位 
9 商品説明
 
  ↓
 
Sheet1
 
  C    D  E
6 商品名 単位 商品説明
7 商品名 単位 商品説明
8 商品名 単位 商品説明
 
上記の形にしたいです。

回答
投稿日時: 20/06/26 15:41:01
投稿者: WinArrow
投稿者のウェブサイトに移動

案山子 さんの引用:
WinArrow さんの引用:
回答者には、あなたのPCの画面は見えません。
列番号、行番号を使って、表のレイアウトを説明しましょう。

 
大変失礼いたしました。
<イメージ>
Sheet2
  C    D
6 商品名 単位 
7 商品説明
8 商品名 単位 
9 商品説明
 
  ↓
 
Sheet1
 
  C    D  E
6 商品名 単位 商品説明
7 商品名 単位 商品説明
8 商品名 単位 商品説明
 
上記の形にしたいです。

 
投稿日時: 20/06/25 20:15:53
の私のレスのコードを読み替えてみてください。。
Sheet2 を Sheet1 に
Sheet1 を Sheet2 に
 
行の先頭を「1」を「6」に
列:AをCに
 

回答
投稿日時: 20/06/26 17:46:53
投稿者: WinArrow
投稿者のウェブサイトに移動

アドバイス
 
投稿日時: 20/06/25 09:32:16

> Set myRng = Worksheets("Sheet2").Range("C1:C762,G1:G762")
というコードがあります。
Sheet2は、元データ側ですよね?
 
数式を入力する場合は、
結果を表示したいセルに数式を入力します。
VBAでやる場合も同じ考え方をします。
数式を入力するセルを基準にして、元データとの相対位置を数式で組み立てる
というように考えましょう。
 
また、
>Range("C1:C762,G1:G762")
のように記述すると、連続領域として扱うことになるので
飛び飛びのセルの場合は、範囲指定することは、避けるべきです。
 

投稿日時: 20/06/29 10:17:35
投稿者: 案山子

WinArrow様
 
ご回答ありがとうございます。
指摘されたことを踏まえて、もう少し考えてみます。
ありがとうございました。