다른 파일 시트에서 문자열 검색

Sub Button1_Click()
    ' variable
    Dim file_path As String '검색파일주소
    Dim sheet_name As String '검색시트명
    Dim search_area As String '검색영역
    Dim search_start As String '검색시작셀
    Dim rng As Variant
    
    ' init
    file_path = Range("I2").Value
    sheet_name = Range("I3").Value
    search_area = Range("I4").Value
    search_start = "H13"
    
    ' get data
    Application.Workbooks.Open FileName:=file_path
    rng = Sheets(sheet_name).Range(search_area).Value
    ActiveWorkbook.Close
 
    ' compare
    Range(search_start, ActiveSheet.Range(search_start).End(xlDown)).Select
    
    Set rMulti = Selection.Cells()

    For Each rCol In rMulti.Columns
        For Each rCell In rCol.Rows
            For i = LBound(rng) To UBound(rng)
                For j = LBound(rng, 2) To UBound(rng, 2)
                    If InStr(rng(i, j), Range(rCell.Address).Value) Then
                        Range(rCell.Address).Offset(0, 1) = "O"
                    End If
                Next j
            Next i
        Next rCell
    Next rCol
        
        
End Sub

 

https://excelmacromastery.com/vba-for-loop/

VBA For Loop - A Complete Guide - Excel Macro Mastery

https://mainia.tistory.com/3877

엑셀 VBA 셀 영역 선택하는 여러가지 방법

 

엑셀 VBA 셀 영역 선택하는 여러가지 방법