| |
変数bookと変数Noの関係に注目して見たら？
book = Range("W137").Value
Select Case book
Case "01"
No = 5
Case "02"
No = 6
Case "03"
No = 7
・
・
Case "29"
No = 33
Case "30"
No = 34
Case Else
Exit Sub
End Select
もし、変数bookの値が数値なら（"01"→1、"02"→2）、
其の数値+4が変数Noの値に成れば善いのでは？
VBAで、文字列を数値に評価する関数は、Val関数、Clng関数等が有ります
Val関数は、空白の文字列、スペースの文字列、数字以外の文字列の場合
0を返すので、この様な場合エラー処理をしなくても善い可能性が有るので
Val関数を使うと善いでしょう？
また、変数bookの範囲は、"01"〜"30"なのでそれ以外の場合、Subを抜けます
それをコードで表すと、以下の様に成ります
Sub 検索値貼付()
Dim rng1 As Range
Dim rng2 As Range
Dim n As Long
Dim z As Long
' Dim book As String
Dim book As Long
Dim No As String
book = Val(Range("W137").Value)
If 1 <= book And book <= 30 Then
No = book + 4
Else
Exit Sub
End If
z = Range("W138").End(xlDown).Row
For n = 3 To 87 Step 3
Set rng1 = Range("B" & n)
Set rng2 = Range("W138:W" & z).Find(rng1.Value, lookat:=xlWhole)
If Not rng2 Is Nothing Then
rng1.Offset(, No).Value = rng2.Offset(, 1).Value
End If
Next n
For n = 90 To 93
Set rng1 = Range("B" & n)
Set rng2 = Range("W138:W" & z).Find(rng1.Value, lookat:=xlWhole)
If Not rng2 Is Nothing Then
rng1.Offset(, No).Value = rng2.Offset(, 1).Value
End If
Next n
Set rng1 = Nothing: Set rng2 = Nothing
End Sub
|
|
Hirofumi
- 05/9/23(金) 0:42 -
