４，最終の選択ボタン コメントアウトの範囲内をfunction（wbSerch）として、値を返して変数を使用したいです Private Sub CommandButton1_Click() Dim wbRepName As String '########## ここから ########## Dim i As Long Dim wb As Workbook Dim wbFullPath As String For i = 1 To Workbooks.Count Set wb = Workbooks(i) If wb.FullName Like "*検索ワード*" Then ' * *に挟まれた文字を含むブックを検索する wbFullPath = wb.FullName Exit For End If Next Dim pos As Long pos = InStrRev(wbFullPath, "\") If (pos > 0) Then wbRepName = Right(wbFullPath, Len(wbFullPath) - pos) End If '########## ここまで ########## Dim rowRep As Long rowRep = Worksheets("シート名").Cells(rowButton, "Q").Value Dim num As Long num = 1 If Workbooks(wbRepName).ActiveSheet.Cells(rowRep + 1, "H") = "" Then Workbooks(wbRepName).ActiveSheet.Range(Workbooks(wbRepName).ActiveSheet.Cells(rowRep + 1, "H"), Workbooks(wbRepName).ActiveSheet.Cells(rowRep + 2, "H")) = _ Worksheets(ShIndex).Range(Worksheets(ShIndex).Cells(3 + (num - 1) * 4, "E"), Worksheets(ShIndex).Cells(3 + (num - 1) * 4 + 1, "E")).Value End If Workbooks(wbRepName).ActiveSheet.Range(Workbooks(wbRepName).ActiveSheet.Cells(rowRep + 4, "H"), Workbooks(wbRepName).ActiveSheet.Cells(rowRep + 7, "H")) = _ Worksheets(ShIndex).Range(Worksheets(ShIndex).Cells(3 + (num - 1) * 4, "F"), Worksheets(ShIndex).Cells(3 + (num - 1) * 4 + 3, "F")).Value End Sub
