The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
.Open "SELECT * FROM tblItemDescription WHERE ItemNumber = '" & Range("A3") & "'"
Dim arrVals
Dim strValues As String
Dim strSQL As String
arrVals = Range("A3:A10").Value
arrVals = Application.Transpose(arrVals)
strValues = Join(arrVals, "','")
strValues = "'" & strValues & "'"
strSQL = "SELECT * FROM tblItemDescription WHERE ItemNumber In(" & strValues & ")"
.Open strSQL
Dim arrVals
Dim strValues As String
Dim strSQL As String
Dim LastRow As Long
' find last row in column A
LastRow = Range("A" & Rows.Count).End(xlUp).Row
' put all the values in column A to the last row in an array
arrVals = Range("A3:A" & LastRow).Value
' array created above is 2 dimensional
' so transpose to get 1 dimensional array
arrVals = Application.Transpose(arrVals)
' join all the values in the array into one long string
' each value is separated by a comma
' we also add a single quote before and after the comma
strValues = Join(arrVals, "','")
' need to add single quote at start and end
strValues = "'" & strValues & "'"
' now we build the SQL statement
' we use the In operator to deal with multiple values
strSQL = "SELECT * FROM tblItemDescription WHERE ItemNumber In(" & strValues & ")"
Option Explicit
Sub BuildSQLStr()
Dim ItemsArr As Variant
Dim ItemsStr As String
Dim ws As Worksheet
Dim i As Long
'I don't know what this is meant to be but I assume it is a recordset therefore CHANGE AS NEEDED.
Dim BLAHBLAH As Object
Set ws = ActiveSheet
'populate an array with the items on the sheet (this assumes there are no blank rows in the list of items)
With ws.Range("a3")
ItemsArr = Range(.Cells(1, 1), .End(xlDown))
End With
'examples:
'WHERE (((Table2.Subject)="ENG")) OR (((Table2.Subject)="MAT")) OR (((Table2.Subject)="ARTS"));
'.Open "SELECT * FROM tblItemDescription WHERE ItemNumber = '" & Range("A3") & "'"
'build the string
For i = LBound(ItemsArr) To UBound(ItemsArr) - 1
ItemsStr = ItemsStr & " ((ItemNumber) = " & ItemsArr(i, 1) & ") OR"
Next i
ItemsStr = ItemsStr & " ((ItemNumber) = " & ItemsArr(i, 1) & ");"
BLAHBLAH.Open "SELECT * FROM tblItemDescription WHERE " & ItemsStr
Set ws = Nothing
End Sub
Private Function PossibleErrorCodeOfActiveCell() As Variant
'To allow filtering of cells with errors (the commented # to the _
right is the error value.
If IsError(CurrentCell) Then
Select Case CurrentCell
Case CVErr(xlErrNA) '2042
PossibleErrorCodeOfActiveCell = "#N/A"
Case CVErr(xlErrValue) '2015
PossibleErrorCodeOfActiveCell = "#VALUE!"
Case CVErr(xlErrDiv0) '2007
PossibleErrorCodeOfActiveCell = "#DIV/0!"
Case CVErr(xlErrName) '2029
PossibleErrorCodeOfActiveCell = "#NAME?"
Case CVErr(xlErrNum) '2036
PossibleErrorCodeOfActiveCell = "#NUM!"
Case CVErr(xlErrRef) '2023
PossibleErrorCodeOfActiveCell = "#REF!"
Case CVErr(xlErrNull) '2000
PossibleErrorCodeOfActiveCell = "#NULL!"
End Select
Else
PossibleErrorCodeOfActiveCell = CurrentCell
End If
End Function
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
Open in new window