CODEWITHSUNDEEP
vbaexcel
eM_Sk
eM_Sk

Reputation: 59

Loop through an array and then move some elements

So, im trying to write a code that will loop through an array and find if that element is in another array, then I'm trying to play with them, as I'm newbie to VBA, some of the code I wrote myself, and some of it I borrow from here (like this fuction below)

To be more clear (as you can see below - link to a screenshot), im looping through column data and looking if each element (in each cell) is on my list to the left. If yes it should extract it next to the data column. But what I wrote so far it is extracting only one of element. I'm not sure where I should add some condition which would work like on the screen below.

https://ibb.co/kn70jb

Sub Find_other()

Dim i, j, As Integer
Dim splitArray() As String



Set dataSheet = Worksheets("Data")
maxRow = dataSheet.UsedRange.Rows(dataSheet.UsedRange.Rows.Count).Row



Range("A2").Select
List_Array = Range(Selection, Selection.End(xlDown)).Value



For i = 2 To maxRow
    fullVal = dataSheet.Cells(i, 3).Value
    splitArray() = Split(fullVal, "|")
    For j = 0 To UBound(splitArray)
        partVal = splitArray(j)
        If isInArray(partVal, List_Array) = False Then
        Cells(i, 4) = partVal
        End If

    Next j
Next i

End Sub


Function isInArray(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
For Each element In arr
    If element = stringToBeFound Then
        isInArray = True
        Exit Function
    End If
Next element
End Function

Thanks for any suggestions.

Upvotes: 0

Views: 85

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3259

The problem you're having is with 

Cells(i, 4) = partVal

This will always put the item it finds into column D, overwriting the previous value each time it finds something new. You can solve this by making a counter variable.

Dim count As Integer

For i = 2 To maxRow
    fullVal = dataSheet.Cells(i, 3).Value
    splitArray() = Split(fullVal, "|")
    count = 4
    For j = 0 To UBound(splitArray)
        partVal = splitArray(j)
        If isInArray(partVal, List_Array) = False Then
            Cells(i, count) = partVal
            count = count + 1 
        End If

    Next j
Next i

This should increase the column number any time that it finds a match and reset it back to the start when you begin checking with a new word

Upvotes: 1

Related Questions