Excel (VBA)

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

 
(Windows 10 Pro : Excel 2013)
指定セルまで結果を求める
投稿日時: 19/11/16 18:37:40
投稿者: ひっちん1

ご教示お願いします
 
下記式で
 
Dim i
    For i = 8 To 10
    
      Cells(i, "H") = WorksheetFunction.VLookup(Range("J8"), Range("リスト!$X$3:$Z$55"), 2, Fals)
        
   Next
 
Cells(i, "H") の指定するセルまでは
  = WorksheetFunction.VLookup(Range("J8"), Range("リスト!$X$3:$Z$55"), 2, Fals)
  の結果が表示されますが
Range("J8")の部分も同様に  For i = 8 To 10 をできるようにするにはどのようにすればよいのかご教示お願いします
 
よろしくお願いします

回答
投稿日時: 19/11/16 20:39:06
投稿者: WinArrow
投稿者のウェブサイトに移動

格納するセルと同じように記述すればよいです。
変数にもデータ型を宣言しましょう。
 
 
Dim i As Long
 
    For i = 8 To 10
        Cells(i, "H").Value = WorksheetFunction.VLookup(Cells(i, "J").Value, Range("リスト!$X$3:$Z$55"), 2, Fals)
    Next

回答
投稿日時: 19/11/16 21:44:40
投稿者: WinArrow
投稿者のウェブサイトに移動

関数の最後に
 
>, Fals)
と書かれているが、
,False)
ですよね・・・
 
 
もしかしたら・・・・
 
掲示板に、コードを手入力していませんか?
 
コードは、コードペインからコピペしましょう。
 
 

投稿日時: 19/11/16 23:36:44
投稿者: ひっちん1

ご教示ありがとうございます
 
 
掲示板に、コードを手入力していませんか?・・・理解が出来ていない為申し上げございません
  
ご教示頂いた式で下記のように記載して動かすと
3行目が黄色く反転してWorksheetFunctionクラスのVLookupプロパティを取得できません。
と 表示されます・・・解決方法はどうすればよいのでしょうか?
申し訳ございませんがよろしくお願いします
 
 Dim i As Long
  
    For i = 8 To 10
     
        Cells(i, "H").Value = WorksheetFunction.VLookup(Cells(i, "J").Value, Range("リスト!$X$3:$Z$55"), 2, False)
        Cells(i, "I").Value = WorksheetFunction.VLookup(Cells(i, "J").Value, Range("リスト!$X$3:$Z$55"), 3, Falce)
        Cells(i, "G").Value = WorksheetFunction.VLookup(Cells(i, "H").Value, Range("リスト!$X$3:$AB$55"), 5, False)
        Cells(i, "F").Value = WorksheetFunction.VLookup(Cells(i, "H").Value, Range("リスト!$X$3:$AB$55"), 4, False)
        Cells(i, "E").Value = WorksheetFunction.VLookup(Cells(i, "H").Value, Range("リスト!$AK$4:$AL$381"), 2, False)
     ・
    ・
    ・
    ・
     
  Next

投稿日時: 19/11/16 23:49:39
投稿者: ひっちん1

実行時エラーは1004と表示されます

回答
投稿日時: 19/11/17 00:06:28
投稿者: simple

>掲示板に、コードを手入力していませんか?・・・理解が出来ていない為申し上げございません
理解しているしていないの話じゃないです。
実際に動かしたコードをコピーペイストしたほうが、
打ち間違いがなくなって、双方ともハッピイになりますよ、って話。
 
さて、
そのエラーは要するに一致するキーがなかった、ということです。
WorksheetFunction.VLookupのかわりに
Application.VLookupとすると、
エラーでとまらないようになります。
そのかわり、#N/Aがセルに書き込まれます。
 
それでもいいでしょうし、
次のようにすれば、書き込む前にメッセージを出すこともできるでしょう。

 v = Application.VLookup(Cells(i, "J").Value, Range("リスト!$X$3:$Z$55"), 2, False)
 If IsError(v) Then
    MsgBox Cells(i, "J").Value & " に一致するデータがない"
 Else
    Cells(i, "H").Value = v
 End If

投稿日時: 19/11/17 00:20:25
投稿者: ひっちん1

ご教示ありがとうございます
 
 

simple さんの引用:
[color=brown]>
そのエラーは要するに一致するキーがなかった、ということです。
 
[/code]

 
という意味ですか・・・ご回答助かります
 
一致するセルはあるので、指定するセルが違っているのか?
表示形式が違うのかと思いますので、今から調べてみます
 
解決しなければまた、質問させていただきます。
ご回答をお願い申し上げます。

回答
投稿日時: 19/11/17 08:42:08
投稿者: WinArrow
投稿者のウェブサイトに移動

VLOOKUP関数で注意すること
 
検索キーと検索されるキー^(表の中の左端)のデータ型が一致していること

回答
投稿日時: 19/11/17 10:43:15
投稿者: WinArrow
投稿者のウェブサイトに移動

↑書き掛け途中で、外出する用事で失礼しました。
 
データ型
とは、「数値」なのか「文字列」なのか
ということです。
 
検索キーが文字列で、表の方の参照キーが数値・・・不一致
検索キーが数値で、表の方の参照キーが文字列・・・不一致
になるということです。
 
表示形式は関係ありません。
 

回答
投稿日時: 19/11/17 12:47:06
投稿者: 半平太

表示形式が関係することはあります。(日付の場合)
 
でも、その議論をする以前にやるべきことは、
「.Value」プロパティを消去することです(文字通り、蛇足です)。
 

・・ = WorksheetFunction.VLookup(Cells(i, "J").Value, Range("リスト!$X$3:$Z$55"), 2, False)
                        ~~↑~~
                          ここ、要らない(有害になること有り)

 

投稿日時: 19/11/17 17:47:24
投稿者: ひっちん1

皆さん
色々とご教示ありがとうございます
大変助かっております
 
一致しないは時間をかけて色々調べて、やっと解決しました
 
解決後になりましたが、追記していただきました
ご教示頂いた文面を拝見させていただいて
回答いただいたとおりのミスでした
 
データ型
とは、「数値」なのか「文字列」なのか
ということです。
  
検索キーが文字列で、表の方の参照キーが数値・・・不一致
検索キーが数値で、表の方の参照キーが文字列・・・不一致
になるということです。

 
表示形式が関係することはあります。(日付の場合)
 
ありがとうございます。
 
 
 
 
追記質問としまして
ネットで色々検索して、下記式を作成して、動かすと期待通りの重複が表示されますが
 
Range("$BA$8:BA50000")のBA50000を
For i = 8 To Cells(Rows.Count, "k").End(xlUp).Row と
できるようにするにはどのようにすればよいのか理解できません
式の記載方法をご教示お願いします
 
 
Sub 重複()
 
       Dim i As Long
  
    For i = 8 To Cells(Rows.Count, "k").End(xlUp).Row '入力最終セルまでカウント
     
            If WorksheetFunction.CountIf(Range("$BA$8:BA50000"), Cells(i, "BA")) > 1 Then
 
            Cells(i, "V") = "重複"
 
        End If
         
        Next i
    
    End Sub

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

最大行を変数に格納します。
 
Sub 重複()
Dim i As Long, MaxRow As Long
MaxRow = Cells(Rows.Count, "k").End(xlUp).Row
For i = 8 To MaxRow '入力最終セルまでカウント
        If WorksheetFunction.CountIf(Range("$BA$8" & ":BA" & MaxRow), Cells(i, "BA")) > 1 Then
             Cells(i, "V") = "重複"
        End If
    Next i
      
End Sub

回答
投稿日時: 19/11/17 20:53:47
投稿者: WinArrow
投稿者のウェブサイトに移動

参照表のセル範囲を
>("$BA$8" & ":BA" & MaxRow)
のように分けて書いてしまいましたが、意味があるわけではなく
("$BA$8:BA" & MaxRow)
でもよいです。

投稿日時: 19/11/17 23:00:33
投稿者: ひっちん1

ありがとうございます。
 
助かりました
 
また最後にご教示頂きました
 
参照表のセル範囲を
>("$BA$8" & ":BA" & MaxRow)
のように分けて書いてしまいましたが、意味があるわけではなく
("$BA$8:BA" & MaxRow)
でもよいです。

 
は助かります
("$BA$8" & ":BA" & MaxRow)・・・の部分が少しは理解できました
 
本当に助かりました
 
今後もご教示をお願いします。
 
ありがとうございます。