Dim myRange As Range, meRange As Range, myAddress As String, i As Integer Set meRange = Range(“AH2:AH1000”) Set myRange = meRange.Find(What:=Range(“R12”).Value, LookIn:=xlValues) If Not myRange Is Nothing Then myAddress = myRange.Address i = 2 Do Cells(i, “BG”).Value = myRange.Offset(, -6).Value Cells(i, “BH”).Value = myRange.Offset(, -5).Value Set myRange = meRange.FindNext(After:=myRange) i = i + 1 Loop Until myRange.Address = myAddress
For j = 1 To 20 With UserForm2.Controls(“Label” & j) .Caption = Cells(j + 1, 59).Value End With With UserForm2.Controls(“Label” & j + 20) .Caption = Cells(j + 1, 60).Value End With Next j
Private Sub CommandButton6_Click() Application.ScreenUpdating = False If UserForm1.TextBox12 = “” Then MsgBox (“電話番号が入力されていません”) Else Range(“R12”).Value = UserForm1.TextBox12.Value Dim myRange As Range, meRange As Range, myAddress As String, i As Integer Set meRange = Range(“AH2:AH1000”) Set myRange = meRange.Find(What:=Range(“R12”).Value, LookIn:=xlValues) If Not myRange Is Nothing Then myAddress = myRange.Address i = 2 Do Cells(i, “BG”).Value = myRange.Offset(, -6).Value Cells(i, “BH”).Value = myRange.Offset(, -5).Value Set myRange = meRange.FindNext(After:=myRange) i = i + 1 Loop Until myRange.Address = myAddress For j = 1 To 20 With UserForm2.Controls(“Label” & j) .Caption = Cells(j + 1, 59).Value End With With UserForm2.Controls(“Label” & j + 20) .Caption = Cells(j + 1, 60).Value End With Next j Unload UserForm1 UserForm2.Show Range(“BG2:BH1000”).Value = “” Else MsgBox “該当者がいません” End If End If Application.ScreenUpdating = True End Sub
If OptionButton1.Value = True Then Range(“W2”).Value = “昭和” ElseIf OptionButton2.Value = True Then Range(“W2”).Value = “平成” ElseIf OptionButton3.Value = True Then Range(“W2”).Value = “令和” ElseIf OptionButton6.Value = True Then Range(“W2”).Value = “予備” End If
年を入力するテキストボックス(TextBox14)の値をセルX2に転記させます。
テキストボックスの設定
Range(“X2”).Value = TextBox14.Text
最後に和暦西暦対照表から探し出した西暦をフォーム(Label35)に表示する。
フォームに表示させるラベルの設定
UserForm1.Label35.Caption = Range(“Y4”).Value
これで変換ボタンで西暦が表示されるようになりました。
変換ボタン登録のVBAコード
Private Sub CommandButton8_Click() If UserForm1.TextBox14.Value = “” Then MsgBox (“年が入力されていません”) Else If OptionButton1.Value = True Then Range(“W2”).Value = “昭和” ElseIf OptionButton2.Value = True Then Range(“W2”).Value = “平成” ElseIf OptionButton3.Value = True Then Range(“W2”).Value = “令和” ElseIf OptionButton6.Value = True Then Range(“W2”).Value = “予備” End If Range(“X2”).Value = TextBox14.Text UserForm1.Label35.Caption = Range(“Y4”).Value End If End Sub