Excel (VBA)

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

 
(Windows 10全般 : Excel 2016)
繰り返しの処理
投稿日時: 20/05/07 12:59:56
投稿者: chokobanana

2つのシートについて、同じ商品名のデータにしたいです。
 
・シート「あいう」、発注表、2行目にA列は日付、B列から商品名
・シート「かきく」、出荷履歴、B列、1行目にタイトル、2行目から商品名
 
@シート「あいう」にあって、シート「かきく」に無い場合:B列に追加
Aシート「かきく」にあって、シート「あいう」に無い場合:無い商品名をメッセージBOXに表示する
 
参考になりそうな構文を見つけ修正しましたが、@が追加されないです、Aのメーッセジを表示させるにはどうしたらいいのかわかりません、教えて頂けないでしょうか。
よろしくお願いいたします。
 
  Sub 商品名の確認()
     
    Dim o As Range
    Dim p As Range
     
     Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
     Set p = Sheets("かきく").Cells(Rows.Count, 2).End(xlUp)
 
    Dim s As Range
    Dim t As Range
    Dim c As Range
 
    
    Set s = Sheets("あいう").Range("B2",o)
    Set t = Sheets("sheet5").Range("B2",p)
 
    For Each c In t.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(s, c) = 0 Then
            MsgBox "がありません"
            Exit For
        End If
    Next
 
    For Each c In s.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(t, c) = 0 Then
            t(t.Count).End(xlUp).Offset(1).Value = c.Value
        End If
    Next
 
 
 
 End Sub
 

回答
投稿日時: 20/05/07 13:20:48
投稿者: WinArrow
投稿者のウェブサイトに移動

内容はよく読んでいません。
 
> Set s = Sheets("あいう").Range("B2",o)
> Set t = Sheets("sheet5").Range("B2",p)
 
↑の構文、おかしくないですか?
 
前者は
With Sheets("あいう")
   Set s = .Range(.Range("B2"), o)
End With
かな?
 
後者は、なぜ、シート名が「Sheet5」なんでしょうか?
 
記述は、前者を参考にしましょう。
 

投稿日時: 20/05/07 13:47:54
投稿者: chokobanana

参考にした構文が上手く修正されていませんでした。
修正してみましたが@はやはり追加されなかったです
 
エラーが出ないので何が原因かわかりません。
@とAをするにはどうしたらいいか教えて頂けないでしょうか。
 
 
  Sub 商品名の確認()
      
    Dim o As Range
    Dim p As Range
      
     Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
     Set p = Sheets("かきく").Cells(Rows.Count, 2).End(xlUp)
  
    Dim s As Range
    Dim t As Range
    Dim c As Range
  
     
    With Sheets("あいう")
      Set s = .Range(.Range("B2"), o)
    End With
     
    With Sheets("かきく")
      Set t = Range(.Range("B2"), p)
    End With
  
    For Each c In t.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(s, c) = 0 Then
            MsgBox "がありません"
            Exit For
        End If
    Next
  
    For Each c In s.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(t, c) = 0 Then
            t(t.Count).End(xlUp).Offset(1).Value = c.Value
        End If
    Next
  
  
  
 End Sub

回答
投稿日時: 20/05/07 15:43:37
投稿者: WinArrow
投稿者のウェブサイトに移動

コードだけでは、どこが、どのように動けばよいかわかりかねます。
 
適当に、
Debug.Print セルの値 とか セルアドレス
を入れて
ステップ実行してみましょう。
意図する動きと違う動きを確認することができます。
 
 

投稿日時: 20/05/07 16:25:54
投稿者: chokobanana

約1か月悩んで自分ではどうしても解決できないのでこちらで質問させていただいております。
まだ説明が不足しているようでしたら教えてください。
 
・シート「あいう」、発注表、2行目にA列は日付、B列から商品名
・シート「かきく」、出荷履歴、B列、1行目にタイトル、2行目から商品名
 
シート「あいう」の商品名とシート「かきく」の商品名を比べて@とAとなるようにしたいです。
  
@シート「あいう」にあって、シート「かきく」に無い場合:B列に追加
Aシート「かきく」にあって、シート「あいう」に無い場合:無い商品名をメッセージBOXに表示する
 
例えば、各シートが下記のような場合、
 
@はシート「あいう」にあって、シート「かきく」に無い「エ」と「オ」をシート「かきく」のB9に「エ」、B10に「オ」と追加。
Aは該当しないのでメーッセージはありません。
 
 
 ◆シート「あいう」・・・ア〜オが商品名
 
   A   B  C  D  E  F 
 1
 2 日付  ア  イ  ウ  エ  オ
 
 ◆シート「かきく」
 
   A   B     C  
 1 日付  商品名
 2 4/3   イ 
 3 4/3   ウ
 4 4/4   ア
 5 4/4   ア
 6 4/5   ウ
 7 4/5   ア
 8 4/5   ア
 
例えば、各シートが下記のような場合、
 
@は該当しないので追加はありません。
Aはシート「かきく」にあって、シート「あいう」に無い、B6の「キ」とB8の「サ」をメッセージで、"キとサがありません"と表示する。⇒メーッセージは何が無いのか分かればいいので違うメーッセージでも問題ありません。
 
 
 ◆シート「あいう」・・・ア〜オが商品名
 
   A   B  C  D  E  F 
 1
 2 日付  ア  イ  ウ  エ  オ
 
 ◆シート「かきく」
 
   A   B     C  
 1 日付  商品名
 2 4/4   イ 
 3 4/4   ウ
 4 4/5   ア
 5 4/6   エ
 6 4/6   キ
 7 4/7   オ
 8 4/8   サ
  
 
 

回答
投稿日時: 20/05/07 16:52:12
投稿者: WinArrow
投稿者のウェブサイトに移動

例1の場合
 
シート:カキクに追加
ということですが、
 
> t(t.Count).End(xlUp).Offset(1).Value = c.Value
このコードでは、追加されず、既存のデータを壊しています。
 
代案
    With Sheets("カキク")
        .Range("B" & .Rows.Count).End(xlup).Offset(1).value = C.Value
   Wnd With
 
 
  例2の場合
>、"キとサがありません"と表示する
   存在しなかった場合、Exit For でループを抜けているので
複数の商品名を表示することはできません。
 

回答
投稿日時: 20/05/07 17:07:18
投稿者: WinArrow
投稿者のウェブサイトに移動

>約1か月悩んで自分ではどうしても解決できない
 
まず、先入観を捨てることから始めましょう。
 
意図する結果にならないときは、
データが、間違っている
もしくは
プログラムが間違っている
のどちらかです。
 
前者でも後者でも、ステップ実行すれば、
かなりの確率で解決できます。
 
どちらにしても、コードを修正することになりますが・・・・
 
ステップ実行する勉強することです。
 
ちょっとした疑問
「発注表」と「出荷履歴」の関係を教えてください。
「発注表」は、自社が発注したデータですよね?
「出荷履歴」は、自社が出荷したデータですか?発注先が出荷した履歴ですか?
 
「発注表」の存在した商品が、「出荷履歴」に存在しない場合、
「出荷履歴」に商品を追加する仕様になっていますが、「出荷日」がない=出荷実績がない=のに
データを追加してもよいのでしょうか?
後日、「出荷履歴」が判明した場合は、どのように対処するのでしょうか?

回答
投稿日時: 20/05/07 23:04:39
投稿者: MMYS

下記は、変数 t のセルアドレスを表示してますが、
まず実行せずに、変数 t はどんな値が出るか、頭の中で想像して下さい。
どんな値を想像してますか。
 
Sub 商品名の確認()
 
    Dim o As Range
    Dim p As Range
 
    Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
    Set p = Sheets("かきく").Cells(Rows.Count, 2).End(xlUp)
 
    Dim s As Range
    Dim t As Range
    Dim c As Range
 
    With Sheets("あいう")
      Set s = .Range(.Range("B2"), o)
    End With
      
    With Sheets("かきく")
      Set t = Range(.Range("B2"), p)
    End With
 
    Debug.Print t.Address
 
    For Each c In t.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(s, c) = 0 Then
            MsgBox "がありません"
            Exit For
        End If
    Next
 
    Debug.Print t.Address
 
    For Each c In s.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(t, c) = 0 Then
            t(t.Count).End(xlUp).Offset(1).Value = c.Value
        End If
    Next
 
    Debug.Print t.Address
 
End Sub
 
それでは、コードを実行して、実際の値はいくつですか。
それは想定した値ですか。
 

chokobanana さんの引用:

@シート「あいう」にあって、シート「かきく」に無い場合:B列に追加
Aシート「かきく」にあって、シート「あいう」に無い場合:無い商品名をメッセージBOXに表示する

希望動作ですが、なぜ提示コートのようした記述したのか、コードの説明は出来ますか。
つまり、コードの動作の概略を日本語で説明して頂きたいのです。
第三者に説明することで、なぜ動かいないのか、理解出来ます。
 

投稿日時: 20/05/08 13:01:14
投稿者: chokobanana

WinArrowさん、ありがとうございます。
@ができるようになりました。
後学の為、何が原因か教え頂けないでしょうか?
自分のはtで範囲指定(B2:最終行)としていしてしまっているから失敗してしまったということでしょうか。
 

WinArrow さんの引用:
>ちょっとした疑問
「発注表」と「出荷履歴」の関係を教えてください。
「発注表」は、自社が発注したデータですよね?
「出荷履歴」は、自社が出荷したデータですか?発注先が出荷した履歴ですか?
 
「発注表」の存在した商品が、「出荷履歴」に存在しない場合、
「出荷履歴」に商品を追加する仕様になっていますが、「出荷日」がない=出荷実績がない=のに
データを追加してもよいのでしょうか?
後日、「出荷履歴」が判明した場合は、どのように対処するのでしょうか?

 
「発注表」:自社が発注したデータです
「出荷履歴」:自社が出荷したデータです
 
「発注表」の存在した商品が、「出荷履歴」に存在しない場合、「出荷履歴」に商品を追加します。
これは後の多種類の分析表を作成するためであり、個数や日付等は入力されていないので影響ありません。
システムから分析用にエクスポートしたデータなので問題ありませんし、「出荷履歴」は出荷とシステムで
連動しておりますので漏れることはありません。
もちろん、出荷と発注にタイムブランクがありますが出荷漏れはシステムでエラーが出るようになっているので大丈夫です。

投稿日時: 20/05/08 14:32:43
投稿者: chokobanana

MMYSさん、すいません。
実行してもアドレスが分かりませんでした。
どっかに表示されるのでしょうか?
 
参考になりそうな構文を見つけ修正したものになります。
一から作成していないので、なぜ提示コードのように記述したかは不明です。
 
 

回答
投稿日時: 20/05/08 15:31:49
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
後学の為、何が原因か教え頂けないでしょうか?
 自分のはtで範囲指定(B2:最終行)としていしてしまっているから失敗してしまったということでしょうか。

 
あちこちのコードのおいしそうなところを
つまみ食いして、作成したコードです。
 
ってことでしょうか?
 
つまみ食いを非難するものではありませんが、
そのままパッチワークみたいに貼り付けてもだめです。
つまり、そのコードの意味や目的を理解しないまま
使ってはいけないということです。
 
・・・・Debug.Printについても知らないようですね・・
 
> Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
このコードの意味は理解できていますか?
これを、文章で書くと、
「(シート:あいうについて)データが格納されている2行目の最右橋セルを求める。」
となります。
しかし、この文章からは、2通りのコードを書くことができます。
1つは、
     Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
もう一つは
     Set o = Sheets("あいう").Cells(2, "A").End(xlToRight)
です。
 
この違いが理解できますか?
文章を直訳すると、後者のようなコードになるでしょう。
しかし、どちらを使うかは、データが入っている状況に依存します。
データが入っている状況次第で、同じ答えにならないことがあります。
・・・・ということを理解した上で、どちらを使うかを決めます。
 
このようか基本的なことを理解していると
>t(t.Count).End(xlUp).Offset(1).Value = c.Value
このコードがあなたの意図していることと合致しているかわかります。
 
 
 
 

投稿日時: 20/05/08 16:04:16
投稿者: chokobanana

 
 Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
 ↓
これは自分が修正した箇所です
2行目の途中に空白セルがある場合の最終列のつもりです
 
 
 Set o = Sheets("あいう").Cells(2, "A").End(xlToRight)
 ↓
これは、空白セルがない場合の最終列ではなかったでしょうか?
 
 
 
t(t.Count).End(xlUp).Offset(1).Value = c.Value
 ↓
これは最終行の下の行に cを持ってくるですよね?
ごめんなさい
Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)の
何が悪いのかが分かりません。

回答
投稿日時: 20/05/08 17:33:30
投稿者: WinArrow
投稿者のウェブサイトに移動

chokobanana さんの引用:

 Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
 ↓
これは自分が修正した箇所です
2行目の途中に空白セルがある場合の最終列のつもりです
 
 
 Set o = Sheets("あいう").Cells(2, "A").End(xlToRight)
 ↓
これは、空白セルがない場合の最終列ではなかったでしょうか?
 
Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)の
何が悪いのかが分かりません。

 
この理解は間違っていません。
例として書かせてもらったコードが悪いということではありません。
 
で、・・・・
>Sheets("あいう").Cells(2, Columns.Count)
は、どこかのセルを指定しているんですが、どこのセルなのか?
わかりますか?
 
これがわかれば、↓のコードが意図しない結果になることも理解できるはずです。
 
chokobanana さんの引用:

t(t.Count).End(xlUp).Offset(1).Value = c.Value
 ↓
これは最終行の下の行に cを持ってくるですよね?
ごめんなさい
 

回答
投稿日時: 20/05/08 18:16:57
投稿者: WinArrow
投稿者のウェブサイトに移動

Debug.Print t.Address
 
というコードを記述すると
変数:tがRangeオブジェクトなので、セル範囲のアドレスが
イミディエイトウィンドウに表示されます。
 
Debug.Print c.Value
は、セルCの値が
イミディエイトウィンドウに表示されます。
 

回答
投稿日時: 20/05/08 20:34:35
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

    t(t.Count).End(xlUp).Offset(1).Value = c.Value
  ↓
これは最終行の下の行に cを持ってくるですよね?

 
検証方法
(1) Debug.Print t(t.Count).Address
 
(2) Debug.Print t(t.Count).End(xlUp).Address
 
(3) Debug.Print t(t.Count).End(xlUp).Offset(1).Address
 
 
 

投稿日時: 20/05/09 09:25:59
投稿者: chokobanana

MMYS さんの引用:
下記は、変数 t のセルアドレスを表示してますが、
まず実行せずに、変数 t はどんな値が出るか、頭の中で想像して下さい。
どんな値を想像してますか。
 
Sub 商品名の確認()
 
    Dim o As Range
    Dim p As Range
 
    Set o = Sheets("あいう").Cells(2, Columns.Count).End(xlToLeft)
    Set p = Sheets("かきく").Cells(Rows.Count, 2).End(xlUp)
 
    Dim s As Range
    Dim t As Range
    Dim c As Range
 
    With Sheets("あいう")
      Set s = .Range(.Range("B2"), o)
    End With
      
    With Sheets("かきく")
      Set t = Range(.Range("B2"), p)
    End With
 
    Debug.Print t.Address
 
    For Each c In t.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(s, c) = 0 Then
            MsgBox "がありません"
            Exit For
        End If
    Next
 
    Debug.Print t.Address
 
    For Each c In s.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(t, c) = 0 Then
            t(t.Count).End(xlUp).Offset(1).Value = c.Value
        End If
    Next
 
    Debug.Print t.Address
 
End Sub
 
それでは、コードを実行して、実際の値はいくつですか。
それは想定した値ですか。
 

 
できましたテストデータでは全て$B$2:$B$800となりました
やはり最終列が範囲指定となっていたのですね
エラーが出ないのでどこが原因か分かりませんでしたが理解できました。
ありがとうございます。
 
Aの方法を知ってましたら教えて頂けないでしょうか。
よろしくお願いいたします。
 

投稿日時: 20/05/09 09:37:05
投稿者: chokobanana

WinArrow さんの引用:

(1) Debug.Print t(t.Count).Address
 
(2) Debug.Print t(t.Count).End(xlUp).Address
 
(3) Debug.Print t(t.Count).End(xlUp).Offset(1).Address
 

 
イミテッドウィンドウを教えて頂き感謝します。
 
(1)$B$800
 
(2)$B$1
 
(3)$B$2
 
(3)は2行目だったんから元のデータを壊していたんですね。
すごく分かりやすいです。
何が原因か理解できました、ありがとうございます。
 
Debug.Print c.Value は実行時エラー91となってアドレスはわかりませんでした。
 
Aは何か方法がありますでしょうか?

回答
投稿日時: 20/05/09 12:20:19
投稿者: MMYS

chokobanana さんの引用:

できましたテストデータでは全て$B$2:$B$800となりました

お聞きしているのは、実行結果ではありません。
ご自身の頭の中のイメージと同じ値ですか。です。
本当に
 
 全て$B$2:$B$800
 
が意図した値ですか。
もし、意図した通りなら、その理由をお聞かせ下さい。
イメージはchokobananaさんの頭の中なので、
説明して貰わないと分かりません。
 
 
chokobanana さんの引用:

@ができるようになりました。

@とは B列に追加 ですか。本当に期待した動きをしてますか。
 
 
chokobanana さんの引用:

Aの方法を知ってましたら教えて頂けないでしょうか。

なぜ、提示されたコードで、その動きをするのでしょう。
それに、ご自身で気づいて下さい。それにはデバックです。
 
デバックですが、
・ローカルウインドウを表示。
・ステップ実行でに変数にカーソルを当てるる。
で、1行づつ変数の変化を確認しなから
意図通りの動きかを確認です。
 
重要なことはchokobananaさん自身が
ご自身で考えた意図通りの動作
をしているかを確認です。
 
他人が書いたサンプルもコードを理解して下さい。
無理解のまま、拝借しても意図通りには動きません。
なお、サンプルコードに分からないキーワードやプロパティ、関数などは
カーソルを当ててて F1 キーて説明が出ます。
 

回答
投稿日時: 20/05/09 13:26:19
投稿者: WinArrow
投稿者のウェブサイトに移動

>Aは何か方法がありますでしょうか?
とは、
>、"キとサがありません"と表示する
のことですすか?
 
この件は、既に回答したはずです。
 
再掲
    存在しなかった場合、Exit For でループを抜けているので
  複数の商品名を表示することはできません。
 
どうすればよいのかは、自分で考えることです。
 
複数はできません。
という回答なので、1つならできるか?
など

投稿日時: 20/05/09 14:14:06
投稿者: chokobanana

WinArrow さんの引用:
>
存在しなかった場合、Exit For でループを抜けているので
複数の商品名を表示することはできません。

 
データ量が多いので無い商品名が必要になります。
Exit For でループを抜けないようにしたらいいのでしょうか?
 
引用:

For Each c In s.SpecialCells(xlCellTypeConstants)
        If WorksheetFunction.CountIf(t, c) = 0 Then
            With Sheets("かきく")
           .Range("B" & .Rows.Count).End(xlup).Offset(1).value = C.Value
        End With
        End If
Next

 
の後に
 
Aシート「かきく」にあって、シート「あいう」に無い場合:無い商品名をメッセージBOXに表示する
 ↓
Aシート「かきく」にあって、シート「あいう」に無い場合:無い商品名をシート「あいう」の2行目の最終列に追加する
 
としたら大丈夫なのでしょうか?

回答
投稿日時: 20/05/09 15:31:45
投稿者: WinArrow
投稿者のウェブサイトに移動

コードの意味がキチンと理解できていないようですね・・・
コードをきちんと理解しないまま、他人が作ったコードで
うまくいった。・・・・と喜んでいるだkではダメです。
 
先ほど(1)(2)(3)に違いが理解できたという回答がありましたが、
おさらいする意味で、次の操作をやってみてください。
 
新しいブックを使う方がよいでしょう。
 
次の操作を
「マクロの記録」でコードを作成します。
(1)セルB10を選択
(2)[Ctrl]+[↑]を押す
マクロ記録終了します。
この時点で、カーソルはB1セルにあるはず。
 
VBEで作成したコードを見ます。
 
    Range("B10").Select
    Selection.End(xlUp).Select
 
このコードを次のように、変更します。
    Range("B10").End(xlUp).Select
 
1行目の.Selectと2行目のSelection
を取って、1行に編集します。
ここで注目するのは、
End(xlUp)
です。
これは、手操作の[Crl]+[↑]に相当します。・・・そうです、セルの移動(上へ)です。
そして、移動ですから、移動の起点が「Range("B10")」です。
このコードでは、
「どこに起点として、どちらにカーソルを移動させるか」
という手操作をイメージできます。
 
手操作をイメージしながらコードを作成しなければ、単なるコードのつぎはぎになってしまいます。
 
 
 
 
 
 
 
 

投稿日時: 20/05/09 16:22:49
投稿者: chokobanana

ごめんなさい
仕事しながらなのでゆっくり勉強する時間もありません
自分で考えてやってみます

投稿日時: 20/05/09 16:24:17
投稿者: chokobanana

どうしても分からないので質問しましたが失礼します。