Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1931 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【70912】検索し、該当した行の列に置き換える シンク 12/1/17(火) 0:50 質問[未読]
【70913】Re:検索し、該当した行の列に置き換える kanabun 12/1/17(火) 9:03 発言[未読]
【70927】Re:検索し、該当した行の列に置き換える シンク 12/1/18(水) 0:42 質問[未読]
【70928】Re:検索し、該当した行の列に置き換える kanabun 12/1/18(水) 9:29 発言[未読]
【70929】Re:検索し、該当した行の列に置き換える kanabun 12/1/18(水) 10:18 発言[未読]
【70946】Re:検索し、該当した行の列に置き換える シンク 12/1/19(木) 22:13 質問[未読]
【70947】Re:検索し、該当した行の列に置き換える kanabun 12/1/19(木) 22:48 発言[未読]
【70982】Re:検索し、該当した行の列に置き換える シンク 12/1/23(月) 15:30 お礼[未読]
【70912】検索し、該当した行の列に置き換える
質問  シンク  - 12/1/17(火) 0:50 -
引用なし
パスワード
   すいません。質問させて頂きます。

シート１
D列にはホスト名、E列にはIPアドレスが記載されているものとします。

シート２
A列には検索対象となるIPアドレス


シート１にてIPを検索し、
一致した行のホスト名をシート２のIPアドレスと置き換えたいのです。
(検索対象はIPは「IP = "1.222.333.2"」と略しています。)

Sub Search()

　　Dim TG As Object

　　
　　IP = "1.222.333.2"
　　Set TG = Worksheets("Sheet1").Cells.Find(IP)
　　If TG Is Nothing Then
　　　　MsgBox "該当無し"
　　Else

　　ここに一致したときの処理を記載したいのですが、
　　お手数ですが、アドバイスをお願いします。

　　End If
End Sub
【70913】Re:検索し、該当した行の列に置き換える
発言  kanabun  - 12/1/17(火) 9:03 -
引用なし
パスワード
   ▼シンク さん：こんにちは〜
>シート１にてIPを検索し、
>一致した行のホスト名をシート２のIPアドレスと置き換えたいのです。
>(検索対象はIPは「IP = "1.222.333.2"」と略しています。)

何を何で置換しようとされたいのか、いまいちハッキリしません。
簡単なシート例をつけて、説明してくださいませんか？

(例)
Sheet1
　　　　　　D　　　　　　　　　E　　　　
1　　　　　ホスト名　　　　　　IPアドレス　
2　　　　　www.aaa.co.jp　　　 123.456.2.1　
3　　　　　www.nn.kk.jp　　　　178.198.3.4　
4　　　　　ppp.qq.rs　　　　　 1.222.333.2　
5　　　　　nnn.ok.qs　　　　　 111.333.444.5　

Sheet2
　　　A　　　　　　　B　　　　
1　IPアドレス
2　1.222.333.2
3
4

このような [Sheet1]と[Sheet2]があって、Sheet1の「ホスト名-IPアドレス」
対応表をもとに、Sheet2のB列にA列IPアドレスに対応するホスト名を書き出
したい、....とかなら、分かるんですが、
質問はそういうことではないようなので。
【70927】Re:検索し、該当した行の列に置き換える
質問  シンク  - 12/1/18(水) 0:42 -
引用なし
パスワード
   kanabun氏、返信ありがとうございます。
理解に苦しむ文章で申し訳ありません。
Sheet2のB列にA列IPアドレスに対応するホスト名を書き出すでほぼ問題ありません。
ただSheet2のB列●●にSheet1のホスト名を代入させたいと考えております。

kanabun氏の例を使わせて頂きます。

Sheet1
　　　　　　D　　　　　　　　　E　　　　
1　　　　　ホスト名　　　　　　IPアドレス　
2　　　　　www.aaa.co.jp　　　 123.456.2.1　
3　　　　　www.nn.kk.jp　　　　178.198.3.4　
4　　　　　ppp.qq.rs　　　　　 1.222.333.2　
5　　　　　nnn.ok.qs　　　　　 111.333.444.5　

Sheet2
　　　A　　　　　　　B　　　　
1　IPアドレス　　　メッセージ
2　1.222.333.2　　　●●にてメール受信。
3
4

お手数を掛けてすいません。
よろしくお願いします。


▼kanabun さん：
>▼シンク さん：こんにちは〜
>>シート１にてIPを検索し、
>>一致した行のホスト名をシート２のIPアドレスと置き換えたいのです。
>>(検索対象はIPは「IP = "1.222.333.2"」と略しています。)
>
>何を何で置換しようとされたいのか、いまいちハッキリしません。
>簡単なシート例をつけて、説明してくださいませんか？
>
>(例)
>Sheet1
>　　　　　　D　　　　　　　　　E　　　　
>1　　　　　ホスト名　　　　　　IPアドレス　
>2　　　　　www.aaa.co.jp　　　 123.456.2.1　
>3　　　　　www.nn.kk.jp　　　　178.198.3.4　
>4　　　　　ppp.qq.rs　　　　　 1.222.333.2　
>5　　　　　nnn.ok.qs　　　　　 111.333.444.5　
>
>Sheet2
>　　　A　　　　　　　B　　　　
>1　IPアドレス
>2　1.222.333.2
>3
>4
>
>このような [Sheet1]と[Sheet2]があって、Sheet1の「ホスト名-IPアドレス」
>対応表をもとに、Sheet2のB列にA列IPアドレスに対応するホスト名を書き出
>したい、....とかなら、分かるんですが、
>質問はそういうことではないようなので。
【70928】Re:検索し、該当した行の列に置き換える
発言  kanabun  - 12/1/18(水) 9:29 -
引用なし
パスワード
   ▼シンク さん：

>Sheet2のB列にA列IPアドレスに対応するホスト名を書き出すでほぼ問題ありません。
>ただSheet2のB列●●にSheet1のホスト名を代入させたいと考えております。

>Sheet2
>　　　A　　　　　　　B　　　　
>1　IPアドレス　　　メッセージ
>2　1.222.333.2　　　●●にてメール受信。

Sheet1の対応表を表引きする方法はいろいろあると思いますが、
ワークシート関数の LOOKUPだと列順が合わないので、
以下のサンプルでは
ワークシート関数のMatchでIPアドレスを拾ってみました。

'Sheets(2) のB列に A列のIPアドレスに対応するホスト名を
'Sheets(1) の対応表から拾いだし(Lookup)、 書き出す→ ●●部分をホスト名に書き換える。
Sub LookupIP()
　　Dim LookupTable As Range
　　Dim c As Range
　　Dim m
　　Dim HostName As String
　　
　　With Worksheets(1)
　　　　Set LookupTable = .Range("D2", .Cells(.Rows.Count, "E").End(xlUp))
　　End With
　　
　　With Worksheets(2)
　　　　For Each c In .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
　　　　　　m = Application.Match(c, LookupTable.Columns(2), 0)
　　　　　　If IsNumeric(m) Then
　　　　　　　　HostName = LookupTable.Item(m, 1)
　　　　　　　　c(1, 2).Value = Replace(c(1, 2), "●●", HostName)
　　　　　　End If
　　　　Next
　　End With
End Sub

Sheet1の表が不十分だったり、
Sheet2のA列に空白行があったり、したときは、エラーが発生します。
また Sheet2 B列に●●が書いてないときは、ホスト名が拾えていても
書き出されません。

したときは
【70929】Re:検索し、該当した行の列に置き換える
発言  kanabun  - 12/1/18(水) 10:18 -
引用なし
パスワード
   なお、Sheet2のA列 IPアドレスがたくさんあるときは
Sheet1の対応表を Dictionaryオブジェクトに記憶しておくと
何度もMatch検索しなくて済むので、効率よくなります

Sub IP_Hostname()
　　Dim dic As Object
　　Dim c As Range
　　
　　Set dic = CreateObject("Scripting.Dictionary")
　　With Worksheets(1)
　　　　For Each c In .Range("E2", .Cells(.Rows.Count, "E").End(xlUp))
　　　　　　dic(c.Text) = c(1, 0).Text　'Key: IPアドレス　 値：ホスト名
　　　　Next
　　End With
　　
　　With Worksheets(2)
　　　　For Each c In .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
　　　　　　If dic.Exists(c.Value) Then
　　　　　　　　c(1, 2).Value = Replace(c(1, 2), "●●", dic(c.Value))
　　　　　　End If
　　　　Next
　　End With
　　Set dic = Nothing
End Sub
【70946】Re:検索し、該当した行の列に置き換える
質問  シンク  - 12/1/19(木) 22:13 -
引用なし
パスワード
   kanabun氏、返信ありがとうございます。
度々、返信が遅くなり申し訳ありません。

とても参考になりました。
申し訳ないのですが、最後に質問させてください。

c(1, 2).Value = Replace(c(1, 2), "●●", dic(c.Value))
IPアドレスを基準に置き換えていると思いますが、
下記のように●●の位置になった場合、
どのように対処すればよろしいでしょうか？

お手数だと思いますが、よろしくお願いします。

Sheet2
　　　A　　　　　　　B　　　　
1　04:14　　　　　　●●にてメール受信。
2　1.222.333.2　　　KKKLLLGGG:DDDRRR:TTT
3

▼kanabun さん：
>なお、Sheet2のA列 IPアドレスがたくさんあるときは
>Sheet1の対応表を Dictionaryオブジェクトに記憶しておくと
>何度もMatch検索しなくて済むので、効率よくなります
>
>Sub IP_Hostname()
>　　Dim dic As Object
>　　Dim c As Range
>　　
>　　Set dic = CreateObject("Scripting.Dictionary")
>　　With Worksheets(1)
>　　　　For Each c In .Range("E2", .Cells(.Rows.Count, "E").End(xlUp))
>　　　　　　dic(c.Text) = c(1, 0).Text　'Key: IPアドレス　 値：ホスト名
>　　　　Next
>　　End With
>　　
>　　With Worksheets(2)
>　　　　For Each c In .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
>　　　　　　If dic.Exists(c.Value) Then
>　　　　　　　　c(1, 2).Value = Replace(c(1, 2), "●●", dic(c.Value))
>　　　　　　End If
>　　　　Next
>　　End With
>　　Set dic = Nothing
>End Sub
【70947】Re:検索し、該当した行の列に置き換える
発言  kanabun  - 12/1/19(木) 22:48 -
引用なし
パスワード
   ▼シンク さん：

>とても参考になりました。
>申し訳ないのですが、最後に質問させてください。
>
>c(1, 2).Value = Replace(c(1, 2), "●●", dic(c.Value))
>IPアドレスを基準に置き換えていると思いますが、
>下記のように●●の位置になった場合、
>どのように対処すればよろしいでしょうか？
>Sheet2
>　　　A　　　　　　　B　　　　
>1　04:14　　　　　　●●にてメール受信。
>2　1.222.333.2　　　KKKLLLGGG:DDDRRR:TTT
>3
>
Sheet2 のIPアドレスは [A2] セルだけですか？
[B1] に ●●を含む文字列があるということですか？

[A2] が 変数c のセルだとすると、 [B2]セルは c(1, 2) です。
[A2] が 変数c のセルだとすると、[B1]セルは c(0, 2) です。
つまり、
　　 c(0, 2).Value = Replace(c(0, 2), "●●", dic(c.Value))
または、Offsetを使えば、
　　 c.Offset(-1, 1).Value = _
　　　　　Replace(c.Offset(-1, 1).Value, "●●", dic(c.Value))
と表しても 同じことです。
【70982】Re:検索し、該当した行の列に置き換える
お礼  シンク  - 12/1/23(月) 15:30 -
引用なし
パスワード
   kanabun氏

返信が遅くなり申し訳ありません。

最後の最後まで本当にありがとうございました。
今後もいろいろと経験を積んで学んでいきたいと思います。


▼kanabun さん：
>▼シンク さん：
>
>>とても参考になりました。
>>申し訳ないのですが、最後に質問させてください。
>>
>>c(1, 2).Value = Replace(c(1, 2), "●●", dic(c.Value))
>>IPアドレスを基準に置き換えていると思いますが、
>>下記のように●●の位置になった場合、
>>どのように対処すればよろしいでしょうか？
>>Sheet2
>>　　　A　　　　　　　B　　　　
>>1　04:14　　　　　　●●にてメール受信。
>>2　1.222.333.2　　　KKKLLLGGG:DDDRRR:TTT
>>3
>>
>Sheet2 のIPアドレスは [A2] セルだけですか？
>[B1] に ●●を含む文字列があるということですか？
>
>[A2] が 変数c のセルだとすると、 [B2]セルは c(1, 2) です。
>[A2] が 変数c のセルだとすると、[B1]セルは c(0, 2) です。
>つまり、
>　　 c(0, 2).Value = Replace(c(0, 2), "●●", dic(c.Value))
>または、Offsetを使えば、
>　　 c.Offset(-1, 1).Value = _
>　　　　　Replace(c.Offset(-1, 1).Value, "●●", dic(c.Value))
>と表しても 同じことです。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1931 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free