| |
尚、GetBranch1、GetBranch2はユーザー定義関数としても使えます
Option Explicit
Public Sub Sample()
Dim i As Long
Dim lngRows As Long
Dim rngList As Range
Dim strProm As String
Set rngList = ActiveSheet.Cells(1, "B")
With rngList
'データ行数を取得
lngRows = .Offset(65536 - .Row).End(xlUp).Row - .Row + 1
End With
'画面更新を停止
Application.ScreenUpdating = False
With rngList
For i = 1 To lngRows
.Offset(i, -1).Value = GetBranch1(.Offset(i).Value)
' .Offset(i, -1).Value = GetBranch2(.Offset(i).Value)
Next i
End With
strProm = "処理が完了しました"
Wayout:
'画面更新を再開
Application.ScreenUpdating = True
Set rngList = Nothing
MsgBox strProm, vbInformation
End Sub
Public Function GetBranch1(vntMark As Variant) As Variant
Dim strSelect As String
Select Case vntMark
Case Is = "1"
strSelect = "北海道"
Case Is = "2"
strSelect = "青 森"
Case Is = "3"
strSelect = "群 馬"
Case Is = "5"
strSelect = "新 潟"
End Select
GetBranch1 = strSelect
End Function
Public Function GetBranch2(vntMark As Variant) As Variant
Dim vntPrefecture As Variant
vntPrefecture = Array("", "北海道", "青 森", "群 馬", "", "新 潟")
GetBranch2 = ""
If Val(vntMark) <= UBound(vntPrefecture) Then
GetBranch2 = vntPrefecture(Val(vntMark))
End If
End Function
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
