セルに直接値を入れたい

[Y.Kaneda]

金田です。教えてください。

vlookupで値をセルに入れたいのですがうまくいきません。
これでも何とか動いているのですが遅くてすっきりしません。

速度アップをするにはどうしたらようでしょうか？
教えてください。
loopは6000回前後です。

よろしくお願いします。

excel2000,excel2003

Range("Y2").Select

Do Until ActiveCell.Offset(0, -16) = ""
contry = "=VLOOKUP(MID(RC[-16],2,4),輸出1!C[-24]:C[-19],4,FALSE)"
port = "=VLOOKUP(MID(RC[-17],2,4),輸出1!C[-25]:C[-20],5,FALSE)"
user = "=VLOOKUP(MID(RC[-18],2,4),輸出1!C[-26]:C[-21],6,FALSE)"
ActiveCell.Offset(0, 0).Value = contry
ActiveCell.Offset(0, 1).Value = port
ActiveCell.Offset(0, 2).Value = user
ActiveCell.Offset(1, 0).Select
Loop
Range("Y:AA").Select
Selection.Value = Selection.Value
Columns("Y:AA").EntireColumn.AutoFit
Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

[SETO Sohei]

こんにちは。

"Y.Kaneda" <y.ka...@jcom.home.ne.jp> wrote in article
<u5cL2wQb...@TK2MSFTNGP02.phx.gbl> ;
>vlookupで値をセルに入れたいのですがうまくいきません。
>これでも何とか動いているのですが遅くてすっきりしません。
>
>速度アップをするにはどうしたらようでしょうか？

(1) ワークシート関数を使うと，再計算コストも高いので使わない。

(2) セルをいちいち読みに行くと遅いので，[輸出1]シートを
　変数配列などで，先にまとめてメモリに読み込んでおく。

(3) セルに値を入れるために Activate する必要は無い。Activateは重い。

(4) このコードなら必要性は薄いかもしれませんが,　
　ScreenUpdating(表示更新)と Calculation(自動再計算)も殺しておく。

* * * * * *

以下のサンプルコードについて

・寝起き頭で，未実行・未チェックのベタ打ちなのでバグだらけかも。
　とくに列名とかズレまくってるかも…

・[輸出1] のA列が数十件～数百件程度で，かつ，正の整数なら、変数配列の
　インデックスにしてしまえば、下記のコードより、更に劇的に速くなると思います。

・他にも, もっと最適化の余地はあるのですが，まあ現行のVlookup利用の
　数倍～数十倍は早くなるだろう，ということで細部はお許し下さいませ＞皆さま

- - - - 8< - - - - - 8< - - - - -

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim 輸出1配列() As Variant '輸出1 のシート内容を格納する変数配列
Dim 配列行数 As Long '輸出1 の行数
Dim 検索キー As Variant 'Vlookup の第一引数に相当する値
Dim 検索行目 As Long '輸出1 の検索結果の行目。#N/Aな場合は 0。
Dim r As Range, l As Long '汎用変数

'変数配列に輸出1を読み込み
With Sheets("輸出1")
With .Cells(1).CurrentRegion '1行目が見出し行とか、途中に空行とか無考慮(_o_)
配列行数 = .Cells.Rows.Count
ReDim 輸出1配列(配列行数, 4)

For Each r In .Rows
輸出1配列(r.Row, 0) = r.Columns("A") '検索キー
輸出1配列(r.Row, 1) = r.Columns("D") 'country
輸出1配列(r.Row, 2) = r.Columns("E") 'port
輸出1配列(r.Row, 3) = r.Columns("F") 'user
Next
End With
End With

'既存ロジックを流用。個人的には With Thisworkbook.Sheets() を明示したいなあ。
Set r = ActiveSheet.Rows(2)

Do Until r.Columns("I") = ""
検索キー = Mid(r.Columns("I"), 2, 4)
検索行目 = 0
'↑Option Base 0 という前提で#N/Aな際, 輸出1配列(0,*)="" を期待…

For l = 1 To 配列行数
If 輸出1配列(l, 0) = 検索キー Then 検索行目 = l: Exit For
Next
r.Columns("Y") = 輸出1配列(検索行目, 1) 'country
r.Columns("Z") = 輸出1配列(検索行目, 2) 'port
r.Columns("AA") = 輸出1配列(検索行目, 3) 'user
Set r = r.Offset(1)
Loop
Range("A1").Select

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
--
SETO Sohei [ PGP Key ID:0x5DF0FA4D ]
Gobo-city, Wakayama, JAPAN
mailto: s...@creamy.nax.ne.jp

[keiji kounoike]

R1C1形式は、普段使わないので、正しく列が反映されているか分かりませんが。
ワークシート関数を使用したやり方ですが、次のマクロでは、どうでしょうか。

Sub Macrotest()
Dim lastrow As Long, num As Long
lastrow = Cells(2, "I").End(xlDown).Row
With Worksheets("輸出1")
num = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
Cells(i, "Y") = Application.VLookup(Mid(Cells(i, "I"), 2, 4), _
.Range("A1:F" & num), 4, False)
Cells(i, "Z") = Application.VLookup(Mid(Cells(i, "I"), 2, 4), _
.Range("A1:F" & num), 5, False)
Cells(i, "AA") = Application.VLookup(Mid(Cells(i, "I"), 2, 4), _
.Range("A1:F" & num), 6, False)
Next
End With
Columns("Y:AA").Replace What:="#N/A", Replacement:="", _

LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

End Sub

[Y.Kaneda]

SETO様
kounoike様

お礼が遅くなり申し訳ありません。
おかげさまで高速に値を書き込むことができました。

SETO様早朝からマクロを組んで戴きしかも全くバグがなく
そのまま利用させて戴きました。
しかも、超高速（瞬時）に処理が終わります。
同じような作業が多くありますので是非、この処理方法を
勉強したいと思います。

kounoike様の方法は、分かり易いので今からつかわさせて
戴きます。

遅くに申し訳ありません。ありがとうございました。
大変、助かりました。

[SETO Sohei]

すみません。やっぱり寝てました・・・。×変数配列　○配列変数ですね。
「輸出1のA列が, 4桁の数値データ」つまり配列変数のインデックスとして
使えるなら，これだけ↓で済む話でした m(_ _)m

- - - - -8< - - - - - -
Dim vAry(9999), r As Range
For Each r In Sheets("輸出1").Cells(1).CurrentRegion.Rows.Offset(1)
vAry(r.Cells(1)) = Array(r.Cells(4), r.Cells(5), r.Cells(6))
Next

For Each r In Range(Range("I2"), Range("I65536").End(xlUp)).EntireRow.Rows
r.Range("Y:AA").Value = vAry(Int(Mid(r.Cells(9), 2, 4)))
Next
- - - - -8< - - - - - -

[keiji kounoike]

処理速度だけを考えるなら、SETOさんのアプローチが正しいと思います。失礼な
書き方かも知れませんが、金田さんのマクロの書き方を見て、SETOさんのコード
はちょっと難しいかなと思い、あえてワークシート関数を使用する前提でスピー
ドアップする一例を書かせて貰いました。まあ、これでも実用に耐えれる速度は
出るかなと。

[Yoshio Kaneda]

SETO様

おはようございます。
第２段目ありがとうございます。

r.Range("Y:AA").Value = vAry(Int(Mid(r.Cells(9), 2, 4)))

「アプリケーション定義またわオブジェクト定義のエラーです」となって
いまだ確認できておりません。

大変申し訳けありませんが、どうすればよいか教えていただけないでしょうか

[SETO Sohei]

"Yoshio Kaneda" <11665...@jcom.home.ne.jp> wrote in article
<OFgmaqub...@TK2MSFTNGP02.phx.gbl> ;

> r.Range("Y:AA").Value = vAry(Int(Mid(r.Cells(9), 2, 4)))
>
>「アプリケーション定義またわオブジェクト定義のエラーです」となって

× r.Range("Y:AA").Value = ～
○ r.Columns("Y:AA").Value = ～

でした。m(_ _)m

[Yoshio Kaneda]

ＳＥＴＯ様

動作確認ができました。
快適に項目が追加できるようになりまし。

ありがとうございます。