|
あ!、「Sub Sample_4」は
Public Sub Sample_5()
Dim i As Long
Dim j As Long
Dim vntMark As Variant
Dim vntSheets As Variant
Dim vntComp As Variant
vntSheets = Array("い", "う", "え", "お")
vntComp = Array("A", "B", "C")
With Worksheets("あ")
vntMark = .Range("G1").Value
For i = 0 To UBound(vntComp, 1)
If vntMark = vntComp(i) Then
For j = 0 To UBound(vntSheets, 1)
Worksheets(vntSheets(j)).Columns(i + 1).Copy _
Destination:=.Columns(j + 1)
Next j
Exit For
End If
Next i
End With
End Sub
と書くのが一般的なのかも解りませんが(If文が1つ少ない)
もし、入力が条件に合わない場合等
Public Sub Sample_6()
Dim i As Long
Dim j As Long
Dim vntMark As Variant
Dim vntSheets As Variant
Dim vntComp As Variant
vntSheets = Array("い", "う", "え", "お")
vntComp = Array("A", "B", "C")
With Worksheets("あ")
vntMark = .Range("G1").Value
For i = 0 To UBound(vntComp, 1)
If vntMark = vntComp(i) Then
Exit For
End If
Next i
'条件以内なら
If i <= UBound(vntComp, 1) Then
For j = 0 To UBound(vntSheets, 1)
Worksheets(vntSheets(j)).Columns(i + 1).Copy _
Destination:=.Columns(j + 1)
Next j
Else
MsgBox "G1セルの入力が条件に合いません", vbInformation
End If
End With
End Sub
合う場合と合わない場合で並べて書けるので、私は「Sample_4」の様に書きます
|
|