検索して該当するセルのとなりのセルを使い処理したい。

質問

また教えて下さい。
ある文字列を検索してヒットした行（これは必ず一つ、またここまではNo.1431372を参考にしてできています）のなかでさらにある文字列で検索をかけてヒットしたセル（これは文字列が一致さえすれば複数ヒットするようにしたいです。）のその隣のセル（ここはoffsetを使うのでしょうが・・・。）に入力されている数値（空白の場合もあります）を使い色々処理をしたいのですが・・・。
そのさいに例えばその複数ある数値の中で最大値を求めるときに（たぶんApplication.Maxなどを使うと思うのですが）、"0"と""を区別して計算し、できれば最大値のあるセルの位置も知りたいのですが、その方法も教えていただきたいです。
状況としては例えば
東京　１０　神奈川　２０　東京　　　東京　１５・・・
という感じです。ちなみに何列までデータが入力されているかも場合によって違います。

回答No.3

papayuka · ベストアンサー

不明な変数が多くて、何をしてるか良く解かりませんが、、、
※１は、わざわざ行番号、列番号に分解する必要も無いです。
　　　s.Offset(0,2).Value
※２も上記と同様で、また、この構文なら j に代入する必要も感じません。
※３は前の段階で""かチェックしてるので j<>"" は不要でしょう。
※４ j2は多分最小値なのでしょうが、ループ前に初期化しておかないと意味がありません。

j2 = -999
For Each s In tw2.Range("B" & frow2, tw2.Range("IV" & frow2).End(xlToLeft))
h1 = s.Row
i1 = s.Column
　If s.Value = f2 Then
　　　If tw2.Cells(h1, i1).Offset(0, 2).Value <> "" Then　'※１
　　　　j = tw2.Cells(h1, i1).Offset(0, 2).Value　　'※２
　　　　If j <> "" And j > j2 Then　　'※３
　 　　　　j2 = j
　　　　End If
　　　End If
　　End If
Next s
If j2 = -999 Then
　 fW2.Cells(e2, "H").Value = ""
Else
　 fW2.Cells(e2, "H") = j2

B2～P2が下記状態とします。
東京,"",-1.5,東京,"",-1.2,神奈川,"",20,東京,"","",東京,"",-0.9
下記で-0.9を表示します。

Sub Test1()
f2 = "東京"
j2 = -999
For Each s In Range("B2:P2")
　If s.Value = f2 Then
　　If s.Offset(0, 2).Value <> "" Then
　　　　j = s.Offset(0, 2).Value
　　　　　　If j > j2 Then j2 = j
　　End If
　End If
Next s
　MsgBox j2
End Sub

こんな書き方もあります。

Sub Test2()
Dim r As Range, uR As Range
Set uR = Nothing
For Each r In Range("B2:P2")
　If r.Value = "東京" Then
　　 If uR Is Nothing Then
　　　　Set uR = r.Offset(0, 2)
　　 Else
　　　　Set uR = Union(uR, r.Offset(0, 2))
　　 End If
　End If
Next r
　MsgBox Application.Max(uR)
End Sub

お礼

変数の設定部分を抜いてしまって申し訳ありません。
ですが、Test1の方のマクロを活用して自分のやりたい処理をすることに成功しました。（Test2の方は自分の方でちょっと変えたりしたためかうまくいきませんでした。）
何度もご迷惑をおかけしましたがありがとうございます。

回答No.2

papayuka

> tW.Range("B" & fRow) = fR.Offset(0, 1)の部分でエラーが出ます。

ここは ' でコメントアウトしてますのでエラーは出ないハズです。
そちらでどんなコードを書かれているか、どんなデータで処理しているか見えないので原因は解りません。

> どうやったら行、列を数値で取り出せるのでしょう？

少し簡単にしましょう。
For Each ～ Next はコレクションの各要素を処理出来ます。
下記は A1:C3のそれぞれの「アドレス」「値」「行番号」「列番号」を表示します。

Sub aaa()
Dim r As Range
　For Each r In Range("A1:C3")
　　　MsgBox r.Address & "の値は、" & r.Value & vbCrLf & _
　　　　"行は" & r.Row & "で列は" & r.Column
　Next r
End Sub

> 数値の候補が-9,-15,"",-12だとした場合0が最大値になってしまう

ん？こちらの環境では下記は -9 を表示しますよ。

Sub bbb()
　Range("A1:D1") = Array(-9, -15,"", -12)
　MsgBox Application.Max(Range("A1:D1"))
End Sub

補足

最初の部分については私のコピーミスです。大変申し訳ありません。どうもFor Each ～ Next自体には問題がないようですがうまくいきません。例えばFor Each s In tW2.Range("B" & fRow2, tW2.Range("IV" & fRow2).End(xlToLeft))
h1 = s.Row
i1 = s.Column
If s.Value = f2 Then
If tW2.Cells(h1, i1).Offset(0, 2).Value <> "" Then
j = tW2.Cells(h1, i1).Offset(0, 2).Value
If j <> "" And j > j2 Then
j2 = j
End If
End If
End If
Next s
If j2 = -999 Then
fW2.Cells(e2, "H").Value = ""
Else
fW2.Cells(e2, "H") = j2
というようにして（この場合条件に該当するセルの２つとなりのセルに数値が入っているのですが）、そのなかで最大値（－９９９になるほど値が小さくなることはないため便宜上こうしています）を出そうとしているのですがうまくいきません（該当する条件があるのに反応がない）し、
fW2.Cells(e2 - 1, "H").Value = Application.Max(tW2.Cells(fRow2, "F").Value, tW2.Cells(fRow2, "K").Value, tW2.Cells(fRow2, "P").Value, tW2.Cells(fRow2, "U").Value, tW2.Cells(fRow2, "Z").Value)
とやると、やはり該当部分がマイナス値か空白しかないと０を返してきます。

回答No.1

papayuka

ここを

Else
'　 tW.Range("B" & fRow) = fR.Offset(0, 1)
　　 For Each r In tW.Range("B" & fRow, tW.Range("IV" & fRow).End(xlToLeft))
　　　　If r.Value = "東京" Then MsgBox r.Address
　　 Next r
End If

とすると、東京を探してアドレスを表示します。

補足

よくわからないのですがうまくいきません。tW.Range("B" & fRow) = fR.Offset(0, 1)の部分でエラーが出ます。rというのは検索範囲内の中でのセルの一つ（？）という意味だと思うのですが、どうやったら行、列を数値で取り出せるのでしょう？また、条件に該当する数値の中で最大値を出してそれを変数に設定する等がしたいのですが、数値の候補が-9,-15,"",-12だとした場合0が最大値になってしまうのですがとにかくこれを何とかしたいです。
やりたいことがうまく説明できていない感もありますが、よろしくお願いします。