エクセルのマクロについて教えて下さい。

質問

皆様よろしくお願いします。シート１に５桁の管理番号が不規則に並んでいます。およそ２３０個の管理番号があります。シート２には管理番号とデータが一覧表になっています。シート１の管理番号の下２行にシート２を参照する式（=sheet2!H3および=sheet2!W3)を手打ちで入れています。エクセルのマクロを使ってシート１の管理番号の下に値を入れるのではなく式を入れることはできますか。なお、シート１は横のセルが８０、縦のセル１５０の中に管理番号が不規則に並んでいます。シート２にはB列に管理番号H・W列にデータが並んでいます。このようなファイルが８０位ありますので手打ちではなくマクロで自動化を図りたいと思います。officeXPを使用しています。よろしくお願いします。

回答No.3

imogasi · ベストアンサー

いっていることは
Ｓｈｅｅｔ1
管理番号は５桁
約２３０個
８０列ｘ１５０行の中に散在
直下の下２行には管理番号がない？。
そこへ参照セルを入れる。
不規則出現
どのように管理番号のセルを探すのか。
目印は何か、またあるのか。
目でみりゃ判るでなく、コンピュター的に
どう判別するのか。
’－－－－－
Ｓｈｅｅｔ２
データがある
Ｂ列に管理番号、ＨＷにデータがペア
ーーーーー解法
Ｓｈｅｅｔ１で管理番号はどのように見つけるのか。
（補足要求します）
それが判れば、その番号でＳｈｅｅｔ２のＢ列を
ＶＬＯＯＫＵＰ関数（ＶＢＡの中でも使える）で該当行を探して見つける。そして、”＝Ｓｈｅｅｔ２！Ｗ”＆該当行のように
（Ｈ，Ｗ列）その文字列をＳｈｅｅｔ１管理番号セルの同列１つ下、および同列２行下のセルにそれぞれセットすればよい。Ｆｏｒｍｕｌａプロパティをセットする。

お礼

回答ありがとうございました。
「”＝Ｓｈｅｅｔ２！Ｗ”＆該当行のように」が参考になりました。どうもありがとうございました。
なお、VLOOKUP関数から行を探す方法が分かりませんでした。
稚拙なプログラムですが皆様のお陰で出来ましたので、この場を借りてご報告いたします。ありがとございました。
Sub harituke()
Dim k_bangou As String
Dim yoko As Integer
Dim tate As Integer
Dim gyo As Integer
Dim i As Integer
Dim r As Integer
yoko = 80
tate = 150
Worksheets("sheet1").Select
Range("A1").Select
For r = 1 To tate
　For i = 1 To yoko
　k_bangou = ActiveCell.Value
If False <> IsNumeric(k_bangou) And 5 = Len(k_bangou) Then
　 Worksheets("sheet2").Select
　　　　　Range("B2").Select
　　　　　gyo = 2
　　　　　Do While ActiveCell.Value <> ""
　　　　　 If k_bangou = ActiveCell.Value Then
　　　　　　 Worksheets("Sheet1").Cells(r + 1, i).Formula = "=sheet2!H" & gyo
　　　　　　 Worksheets("Sheet1").Cells(r + 2, i).Formula = "=sheet2!W" & gyo
　　　　　　Exit Do
　　　　　End If
　　　　　gyo = gyo + 1
　　　　　ActiveCell.Offset(1, 0).Select
　　　　　Loop
　　　　 Worksheets("sheet1").Select
　　　End If
　　　ActiveCell.Offset(0, 1).Select
　Next
　ActiveCell.Offset(1, -yoko).Select
Next
End Sub

回答No.4

imogasi

＞VLOOKUP関数から行を探す方法が分かりませんでした
参考までにＶＬＯＯＫＵＰ関数のＶＢＡでの使い方
を出しておきます。
Sub test01()
Worksheets("Sheet5").Activate
n = Val(InputBox("番号＝"))
x = WorksheetFunction.VLookup(n, Range("a1:b5"), 2, False)
MsgBox x
End Sub
Ａ１：Ｂ５にはたとえば
Ａ列　　Ｂ列
1 a
3 d
4 g
7 h
8 k
プログラムを実行すると、数を聞いてくるので、７と入れればｈと表示されます。

お礼

ＶＬＯＯＫＵＰ関数の使い方を示していただき、ありがとうございます。
勉強のためＶＬＯＯＫＵＰ関数を使ってプログラムを書き変えてみます。

回答No.2

guruguru2

こんばんわ。

式の設定はFormulaプロパティで出来るみたいです。

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"
こんな感じです。↑

お礼

回答ありがとうございました。
「Worksheets("Sheet1").Range("A1").Formula =」が参考になりました。
おかげさまで解決いたしました。
ありがとうございました。

回答No.1

Wendy02

こんばんは。
＞（=sheet2!H3および=sheet2!W3)を手打ちで入れています。
H3 に対して、連動しているのですね。

もしそうでしたら、本来は、検索範囲を狭めたいところですが、こちらでは分かりませんので、UsedRangeを使用しました。

Sub EnterFormula()
　Dim Sh1 As Worksheet, Sh2 As Worksheet
　Dim c As Range, f1 As Variant
　Set Sh1 = Worksheets("Sheet1")
　Set Sh2 = Worksheets("Sheet2")
　Application.ScreenUpdating = False
　For Each c In Sh1.UsedRange　'UsedRangeを使いました
　　If Not c.HasFormula Then
　　　f1 = Application.Match(c.Value, Sh2.Columns(8), 0)
　　　If Not IsError(f1) Then
　　　　'式代入
　　　　c.Offset(1).Formula = "=" & Sh2.Name & "!H" & f1 '管理番号
　　　　c.Offset(2).Formula = "=" & Sh2.Name & "!W" & f1
　　　End If
　　End If
　Next c
　Set Sh1 = Nothing : Set Sh2 = Nothing
　Application.ScreenUpdating = True
End Sub

お礼

回答ありがとうございました。
ｗｅｎｄｙ０２様にはプログラムを作っていただき、お手数をお掛けいたしました。
最初に書けばよかったのですが、マクロは初心者で今勉強中です。
私にはこのプログラムの内容が難しく、応用が出来ませんでした。どうもすいません。
この内容が理解できるよう勉強を頑張ります。
ありがとうございました。