How To Concatenate String Correctly At End Of Array In Vba?

How can i concatenate the following string to make an SQL statement? Dim sql = 'SELECT * FROM users with (nolock) WHERE' Dim sql2 = '(ownerID = '' For Each item In arrExc

Solution 1:

You could have modified the last answer I gave to your question... see here

But I've done it for you

Sub test()

    Dim strQuery AsStringDim strVals AsStringDim rngTarget As Range
    Set rntTarget = Range("A1:A7")

    Dim varArr
    Dim lngRow AsLongDim myArray()
    varArr = rntTarget.Value2

    ReDim myArray(1To UBound(varArr, 1))

    For lngRow = 1To UBound(varArr, 1)
        myArray(lngRow) = varArr(lngRow, 1)
    Next

    strVals = "('" & Join$(myArray, "','") & "') "

    strQuery = "SELECT * FROM users WHERE "For lngRow = LBound(myArray) To UBound(myArray)
        strQuery = strQuery & "( owner = '" _
            & myArray(lngRow) + "' or renterID = '" _
            & myArray(lngRow) & "') or "Next

    strQuery = Left(strQuery, Len(strQuery) - 4)

    Debug.Print strQuery

EndSub