Read an array from a worksheet cell

[FryGirl]

Right now, my process is to copy the contents of an array from the worksheet and then paste that array into the VBA.

The array is actually produced by =""""&TEXTJOIN(""", """,TRUE,IF($C$2:$C$4="Yes",$B$2:$B$4,""))&""""

I take the output and paste it into the code below. myArray(1) = Array("Year", "Month", "Day")

Instead of copying and pasting the results of TEXTJOIN as the array, can this be read straight into the code?

Sub aTest()
    Dim myArray(1 To 2) As Variant
    Dim i               As Long
    Dim rng             As Range
    
    myArray(1) = Array("Year", "Month", "Day")
    myArray(2) = Array("1", "2", "3")
    
    With Sheets(1)
        For i = LBound(myArray(1)) To UBound(myArray(1))
            Set rng = .Rows(2).Find(what:=myArray(1)(i), Lookat:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns, MatchCase:=True)
            If Not rng Is Nothing Then
                With rng.Offset(-1)
                    .Value = myArray(2)(i)
                    .Interior.Color = vbYellow
                End With
            End If
            Set rng = Nothing
        Next i
    End With
    
End Sub

 

[Bosquedeguate]

can you send a sample row of data and what you the Sub to do with data that matches your FIND criteria?

 

[FryGirl]

Thank you Bosquedeguate, I have it straightened out now.

​

 

[Fluff]

In future please do not mark a post as the solution, if it does not contain one. Thanks

 

[FryGirl]

Got it. Thanks