열에 문자열이 포함된 경우 열 번호 나열 (List column numbers if columns contain string)

문제 설명

열에 문자열이 포함된 경우 열 번호 나열 (List column numbers if columns contain string)

통합 문서의 sheet1에서 "dog" 문자열을 검색하는 코드가 있습니다. 이 문자열은 시트에 여러 번 나타날 수 있으며 해당 열에서 문자열이 발견되면 열 번호의 벡터를 제공합니다. (개는 각 열에 한 번만 나타날 수 있습니다). 시트에 이 매크로를 할당하는 버튼이 있습니다.

Option Explicit


Sub mymacro2()
Dim dog() As Integer
Dim coldog As Range
Set coldog = Sheets(1).UsedRange.Find("dog", , xlValues, xlWhole)
Dim i As Integer
i = 0
ReDim dog(0)
dog(i) = coldog.Column
Do
    i = i + 1
    ReDim Preserve dog(i)
    Set coldog = Sheets(1).UsedRange.FindNext(coldog)
    dog(i) = coldog.Column
Loop While dog(i) <> dog(0)

ReDim Preserve dog(i ‑ 1)



Sheets(1).Cells(1, 1).Resize(1, UBound(Application.Transpose(dog))) = dog
'above line is displaying the vector on the sheet for testing purposes
Set coldog = Nothing

ReDim dog(0)


End Sub

매크로는 내가 원하는 벡터를 제공합니다. 즉, "dog" 문자열을 찾을 수 있는 열을 알려줍니다.

이제 코드를 수정하거나 완전히 새로운 코드를 만들고 싶습니다. sheet2의 1열에 있는 문자열 목록의 각 문자열에 대해 동일한 작업을 수행합니다. 열 번호가 있는 모든 벡터는 열 정보가 있는 문자열과 이름이 같아야 합니다. 위의 코드에서 수동으로 수행하는 것처럼.

요점은 동일한 작업을 수행해야 하는 약 130개의 동물 목록이 있다는 것입니다. Excel VBA에서 이를 수행하는 가장 좋은 방법은 무엇입니까?

참조 솔루션

방법 1:

You have to store all the animals in another Array and call the given actions for each of them. Also your code has quite a few redundant parts. The sample code below should give you a good grasp to understand how to face this problem (as said via comment by Mehow, we are not here to write codes for you).

Dim totAnimals As Integer, i As Integer
totAnimals = 3
ReDim animals(totAnimals ‑ 1) As String
animals(0) = "dog"
animals(1) = "cat"
animals(2) = "mouse"
'etc.

maxMatches = 100 'Maximum number of matches per animal. better don't make this value too big
ReDim matchCount(totAnimals ‑ 1) 'This counter goes from 1 to maxMatches
ReDim matchCols(totAnimals ‑ 1, maxMatches) As Integer

Dim targetRange As Range, tempRange As Range, tempRange2 As Range
Set targetRange = Sheets("sheet2").Columns(1)

For i = 0 To totAnimals ‑ 1
    Set tempRange = targetRange.Find(animals(i), , xlValues, xlWhole)
    If (Not tempRange Is Nothing) Then
        If (matchCount(i) + 1 <= maxMatches) Then
            matchCount(i) = matchCount(i) + 1

            matchCols(i, matchCount(i)) = tempRange.Column
            Dim startAddress As String: startAddress = tempRange.Address
            Set tempRange2 = tempRange
            Do
                Set tempRange2 = targetRange.FindNext(tempRange2)
                If (Not tempRange2 Is Nothing) Then
                    If (tempRange2.Address = startAddress) Then Exit Do
                Else
                    Exit Do
                End If
                If (matchCount(i) + 1 > maxMatches) Then Exit Do
                matchCount(i) = matchCount(i) + 1
                matchCols(i, matchCount(i)) = tempRange2.Column
            Loop While (Not tempRange2 Is Nothing)
        End If
    End If
Next i

(by steinbitur、varocarbas)

참조 문서

1. List column numbers if columns contain string (CC BY‑SA 3.0/4.0)