Myujth

once again an issue i cant really find quite the right answer through searching even though it feels like iam close.
My goal is to check in one column if each cell contains (besides other values) a letter from my array.

So the cells look something like 123A.
My array conatains value A,C,D,X,Y,Z.
Both just example values.
No my If statment should be true if the cell contains any of the letters, so for the example it should be true.
For the cell Value 123B it should be wrong (no B in array).

So far i have found a "IsinArray" Function that appears to be working but checks for specific values but what i would need is closer to ---> "*" & IsinArray & "*"

The function i found looks like this:

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
 IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

Also my For statement to loop through the column is working (I'd say ^^)

Thank you very much in advance, Iam also open to "creative" solutions if you have any new ideas how to do it better.

Try,

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
 Dim s As String, i As Integer
 Dim a As Variant
 For i = 1 To Len(stringToBeFound)
 s = Mid(stringToBeFound, i, 1)
 For Each a In arr
 If s = a Then
 IsInArray = True
 Exit Function
 End If
 Next a
 Next i
End Function

Sorry to answer my own question (corrections/feedback to this solution is welcome of course)

I tried it like this and i guess it should work (cant really test because other parts of my makro arent working)
It is kinda unecessary complicated and maybe slow but id say it could work:

For i = 1 To VarAnzahlZeilen
 Set rng = Worksheets("Filter").Range(Cells(i, VarNutzerSpalte), Cells(i, VarNutzerSpalte))

 If IsInArrayValue(ArrAuswahlNutzer, rng) Then
 Worksheets("Import").Rows(j).Copy
 Worksheets("Filter").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 End If

Next i

Uses this function by QHarr (with only the array name changed)

Public Function IsInArrayValue(ByVal testArray As Variant, ByVal rng As Range) As Variant
 Dim i As Long, testString As String
 testString = rng.Text
 If rng.Cells.Count > 1 Then
 IsInArrayValue = CVErr(xlErrNA)
 Exit Function
 End If
 For i = LBound(testArray) To UBound(testArray)

 If InStr(testString, testArray(i)) > 0 Then
 IsInArrayValue = True
 Exit Function
 End If
 Next
 IsInArrayValue = False
End Function

Thanks alot @ QHarr and also @ Dy.Lee !

You could swop it around

Option Explicit

Public Sub Test()
 Dim testArray(), cellValue As String, rng As Range
 Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1") '<== contains
 testArray = Array("A", "C", "D", "X", "Y", "Z")
 Debug.Print IsInArrayValue(testArray, rng)
End Sub

Public Function IsInArrayValue(ByVal testArray As Variant, ByVal rng As Range) As Variant
 Dim i As Long, testString As String
 testString = rng.Text
 If rng.Cells.Count > 1 Then
 IsInArrayValue = CVErr(xlErrNA)
 Exit Function
 End If
 For i = LBound(testArray) To UBound(testArray)

 If InStr(testString, testArray(i)) > 0 Then
 IsInArrayValue = True
 Exit Function
 End If
 Next
 IsInArrayValue = False
End Function

If using as an UDF you could either pass the array in as shown above or if array doesn't change you could move into the function itself. Personally, I prefer passing the array as an argument to the function as more flexible. I can't work out where your row to copy is coming from. Your comment posted as an answer uses a j variable that doesn't appear to be involved in the shown loop and the row is copied from another sheet. So below will not work directly but gives you a framework.

Public Function IsInArrayValue(ByVal rng As Range) As Variant
 Dim i As Long, testString As String, testArray()
 testArray = Array("A", "C", "D", "X", "Y", "Z")
 testString = rng.Text
 If rng.Cells.Count > 1 Then
 IsInArrayValue CVErr(xlErrNA)
 Exit Function
 End If
 For i = LBound(testArray) To UBound(testArray)

 If InStr(testString, testArray(i)) > 0 Then
 IsInArrayValue = True
 Exit Function
 End If
 Next
 IsInArrayValue = False
End Function

Call:

The comment below is looks like a new question but you probably want something like:

Dim loopRange As Range, rng As Range

With ThisWorkbook.Worksheets("Filter")
 Set loopRange = .Range(.Cells(1, VarNutzerSpalte), .Cells(VarAnzahlZeilen, VarNutzerSpalte))
End With

For Each rng In loopRange
 If IsInArrayValue(ArrAuswahlNutzer, rng) Then
 rng.EntireRow.Copy '<= use Union to gather range if all being pasted in a block somewhere
 End If
Next

A union version might looks like:

Dim loopRange As Range, rng As Range, unionRng As Range

With ThisWorkbook.Worksheets("Filter")
 Set loopRange = .Range(.Cells(1, VarNutzerSpalte), .Cells(VarAnzahlZeilen, VarNutzerSpalte))
End With

For Each rng In loopRange
 If IsInArrayValue(ArrAuswahlNutzer, rng) Then
 If Not unionRng Is Nothing Then
 Set unionRng = Union(unionRng, rng)
 End If
 Set unionRng = rng '<= use Union to gather range if all being pasted in a block somewhere
 End If
Next

If Not unionRng Is Nothing Then

 unionRng.EntireRow.Copy 'destination for paste

End If