エクセルVBAでつまずいています

質問

教えてください。以前別のブックから、抽出条件を指定して、
項目を追加して、ある条件を検査して、それによって追加した項目の列
に対応する値を書き込むというコードを教えていただきました。
質問は、今現在、
With .Range("AH2").Resize(r - 1, 1)
.FormulaR1C1 = _
"=IF(RC[-25]=""AAA"",""aaa""," & _
"IF(RC[-25]=""BBB"",""bbb""," & _
"IF(RC[-25]=""CCC"",""ccc""," & _
"IF(RC[-25]=""DDD"",""ddd"",""xxx"")
の部分で、関数を設定していますが、AIの列にも同じように関数
(VLOOKUP)を設定したいのですが、Resize(r - 1, 1)の意味するところが
しっかり理解していないためできません。
A1形式ですが、例えば、
参照先がD2、A2:J100として
=VLOOKUP(D2,A2:J100,5,FASE)
=VLOOKUP(D2,A2:J100,6,FASE)
=VLOOKUP(D2,A2:J100,7,FASE)
という条件を追加したいのですが、わかりませんでした。
どのようにしたらいいでしょうか。よろしくお願いします。

Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim ms As Worksheet
Dim nb As Workbook
Dim r As Long

Set ms = ThisWorkbook.Worksheets("条件入力")
Set wb = Workbooks.Open(ms.Parent.Path & "\" & ms.Range("D3").Value)
Set ws = wb.Worksheets("元データ")
Set nb = Workbooks.Add
With ws
.Range("Q1").AutoFilter _
Field:=17, _
Criteria1:=">=" & ms.Range("D5").Text, _
Operator:=xlAnd, _
Criteria2:="<=" & ms.Range("F5").Text
With .AutoFilter.Range
r = .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count

If r = 1 Then
MsgBox "抽出対象データ無し。"
wb.Close False
nb.Close False
Set wb = Nothing: Set ws = Nothing
Set ms = Nothing: Set nb = Nothing
Exit Sub
End If
.Copy
End With
End With

With nb.Worksheets(1)
.Paste
With .Range("A1:AG1")
.Interior.ColorIndex = 6
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
.Range("AH1").Value = "追加項目A"
.Range("AI1").Value = "追加項目B"
.Range("AJ1").Value = "追加項目C"
.Range("AK1").Value = "追加項目D"
With .Range("AH2").Resize(r - 1, 1)
.FormulaR1C1 = _
"=IF(RC[-25]=""AAA"",""aaa""," & _
"IF(RC[-25]=""BBB"",""bbb""," & _
"IF(RC[-25]=""CCC"",""ccc""," & _
"IF(RC[-25]=""DDD"",""ddd"",""xxx"")
End With
End With

nb.SaveAs _
Filename:=ms.Parent.Path & "\" & _
Replace(wb.Name, ".xls", "") & "更新データ.xls"

wb.Close False
nb.Close
Set wb = Nothing: Set ws = Nothing
Set ms = Nothing: Set nb = Nothing

End With

End Sub

回答No.1

Wendy02 · ベストアンサー

こんばんは。

マクロとしては、良く書かれていると思います。こういう段階は、VBAを実際にやっている人なら、誰でも経験があるコードだと思います。

とりあえず、回答ですが、

>A1形式ですが、例えば、
>参照先がD2、A2:J100として
>=VLOOKUP(D2,A2:J100,5,FASE)

そのような数式は、循環参照にはならないけれども、データ範囲と検索範囲が重なり合っているわけだから、ありえないことだけれども、以下のようになると思います。

For i = 1 To 4
　Range("K1").Offset(i - 1).FormulaLocal = _
　　 "=VLOOKUP(D2,A2:J100," & i + 4 & ",FALSE)"
Next i

>.FormulaR1C1 = _
>"=IF(RC[-25]=""AAA"",""aaa""," & _
>"IF(RC[-25]=""BBB"",""bbb""," & _
>"IF(RC[-25]=""CCC"",""ccc""," & _
>"IF(RC[-25]=""DDD"",""ddd"",""xxx"")

なお、エラーが出るはずです。最後の部分は、こうなるはずです。
　"IF(RC[-25]=""DDD"",""ddd"",""xxx""))))"

ただ、数式なら、もう少し縮めて、
.FormulaLocal = _
"=IF(SUMPRODUCT(EXACT(I2,{""AAA"",""BBB"",""CCC"",""DDD""})*1),LOWER(I2),""xxx"")"
としてもよいと思います。それから、プロシージャの中のコードは半分ぐらいに分けて、分散化させたほうがよいです。修正しやすくなります。

お礼

Wendy02さんこんばんは。いろいろなことをお教えいただきありがとうございます。For～Nextの方法を教えていただきありがとうございます。
ただ、私の設定の仕方が悪いのか、4つの処理で終わってしまいます。これは
For i = 1 To 4なので、当然の結果でしょうか。

参照先がD2、参照元A2:J100として(例が循環していました。スミマセン)
=VLOOKUP(D2,A2:J100,5,FASE)
=VLOOKUP(D2,A2:J100,6,FASE)
=VLOOKUP(D2,A2:J100,7,FASE)
と、結果により参照参照元の5、6、7列目を参照するということを、データののある最後の行までやっていきたいのですが、・・・。試してみたものの分かりませんでした。この例ではわかりづらいでしょうか？

回答No.2

Wendy02

こんばんは。#1のWendy02です。

もともと、数式の立て方の問題ですね。

=VLOOKUP(D2,A2:J100,5,FASE)　'←FASE ではなく、FALSE ですね。
　　↓
=VLOOKUP(D2,A2:J100,5,FALSE)

検索値　D2 は、A2:J100 の範囲の中にありますから、そこを直さないといけません。

5が始まりですから、

E -> J までですから、
(5)...(10)

実際は、

For i = 1 To 6　　'←数を増やせばよいです。
　Range("K1").Offset(i - 1).FormulaLocal = _
　　 "=VLOOKUP(D2,A2:J100," & i + 4 & ",FALSE)"
Next i

ということになります。