Excel (VBA)

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

 
(Windows 7 Professional : Excel 2007)
存在チェック
投稿日時: 18/10/16 13:45:22
投稿者: FILETUBE

こんにちは。
1つ教えて頂けないでしょうか。
 
下記はBookを選択しG列の値が別のマスターのBook(AB列)に存在しなかった場合
黄色に塗りつぶす処理です。
 
wksはチェックするWorksheet
chkwbはマスターのWorkbook
 
Dim rmst As Range
Dim r As Range
 
For Each r In wks.Range("G2:G100")
           If r.Offset(0, 0).Value <> "" Then
              Set rmst = chkwb.Sheets(1).Range("AB:AB").Find(what:=Trim(r.Value), LookAt:=xlWhole)
              If rmst Is Nothing Then
                 r.Offset(0, 0).Interior.Color = RGB(255, 255, 0)
              Else
                 r.Offset(0, 0).Interior.Color = RGB(255, 255, 255)
              End If
              Set rmst = Nothing
           End If
Next
 
これ自体はOKなのですが、G列の値とマスタのAB列の値の上8桁で比較したいのです。
 
例えばG列の値がABCDEFG-
マスタのAB列の値がABCDEFG-HIJなら存在OKにしたいのです。
 
この場合上記のコードをどのように変更するとよいのか
分かる方おられましたら、教えて頂けないでしょうか。
 
宜しくお願いします。

回答
投稿日時: 18/10/16 18:12:57
投稿者: Suzu

Findメソッド のみを使って、上8桁のみ で比較 はできないと思います。
 
・初めから 8桁の データを用意し、Findメソッドにて検索をする
      →作業列を作る
 
・1セル 対 1セルの値を順次総当たりで上8桁のみ比較
      →作業列は使わないで、VBAの中でループ処理で1セル毎の値を取得しLEFT関数で8桁取得し比較
 
 
Find で 確認しても良いのですが、
何度も マスターの【セル】にアクセスしに行くのは効率が悪そうなので、
事前に 8桁のみを表示する作業列を作っておいて その行を配列に入れちゃい、Filter を使うかな。。
 
あと、条件分岐で都度、色を変えていますが、先に範囲の色を RGB(255, 255, 0) にしておいて
合致データがあったら、RGB(255, 255, 255) にしますかね。
 
 
チェック対象のワークシートのAB列の値の上8桁を AC列に表示させます。
要はAC列を作業列として使う例。
 
  Dim MasterArray As Variant
  Dim rst As Variant
  Dim i As Long
 
  chkwb.Sheets(1).Range("AC:AC").FormulaR1C1Local = "=LEFT(TRIM(RC[-1]),8)"
  MasterArray = WorksheetFunction.Transpose(chkwb.Sheets(1).Range("AC:AC"))
 
  wks.Range(wks.Cells(2, 7), wks.Cells(100, 7)).Interior.Color = RGB(255, 255, 0)
  For i = 2 To 100
    rst = Filter(MasterArray, Left(Trim(wks.Cells(i, 7)), 8))
    If UBound(rst) = 0 Then
      wks.Cells(i, 7).Interior.Color = RGB(255, 255, 255)
    End If
  Next i

回答
投稿日時: 18/10/16 19:47:50
投稿者: sy

Suzu さんの引用:
Findメソッド のみを使って、上8桁のみ で比較 はできないと思います。
出来ますよ。
 
完全一致検索のままで、
what:=Trim(r.Value) & "*"
と後ろにワイルドカードを使えば可能です。
質問のコード修正に関しては、ワイルドカードだけで良いですけど、今のままだとセルの色を直接変更するコードだと元から色付けしていた場合などは具合悪いと思うんですけど。
 
非表示の稼業シート」を予め作っておいて、そこにコピー元ブックのAB列の値を転記だけして、色付けは条件付き書式にすれば、コードも値転記の1行の記述だけで良いですよ。
 
コードは以下の1行に変更して、
chkwb.Sheets(1).Range("AB:AB").Copy Sheets("作業").Range("A1")

条件付き書式でG2〜G100に以下の数式を設定
=COUNTIF(作業!A:A,TRIM(G2)&"*")=0
これだけで出来ます。

回答
投稿日時: 18/10/16 19:57:27
投稿者: sy

作業シートじゃなくても、チェックするシートの作業列に貼付けても良いです。
表示させたくなければその列を非表示にしておけば良いです。
 

仮にI列が作業列とすれば、
chkwb.Sheets(1).Range("AB:AB").Copy wks.Range("I1")

条件付き書式でG2〜G100に以下の数式を設定
=COUNTIF(I:I,TRIM(G2)&"*")=0

投稿日時: 18/10/16 21:09:52
投稿者: FILETUBE

回答ありがとうございます。
色々なご指導ありがとうございました。
早速検証してみたいと思います。
検証後、改めて投稿させて頂きます。

回答
投稿日時: 18/10/17 11:57:45
投稿者: Suzu

sy さんの引用:
Suzu さんの引用:
Findメソッド のみを使って、上8桁のみ で比較 はできないと思います。
出来ますよ。

 
当方は、マスター / 被検索対象 共に 8桁以上の場合もあり、
上位 8桁にてマッチングを行う必要があると捉えました。
 
それで、Findでは できないと回答しました。

投稿日時: 18/10/17 11:59:44
投稿者: FILETUBE

 今回はありがとうございました。
ワイルドカードを使い対応させて頂きました。
 
もう1点お聞きしたいことがありますので
また宜しくお願いします。