Excel 2007 VBA for a column if I don't know how many rows

Do you mean you want to create a single SQL statement with the criteria coming from every row in column A, starting at row 3?

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

Select all Open in new window

Hi imnorie. Yes that is correct, I would like to use every row in column A starting at row 3. But I won't know how many rows there will be so what would I put in arrVals = Range("A3:A10").Value since there won't always be 10 rows?

Would you mind explaining the code so I understand how it works?

Oops, didn't actually mean to post that yet - it's not quite finished.

Here's the complete thing with comments that hopefully explain what's happening.

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 & ")"

Select all Open in new window

Note, if ItemNumber is a numeric field then the single quotes aren't needed.

hi printmedia,

Try this out, you may need to change the number of brackets.

Select all Open in new window

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

Select all Open in new window

hth
Rob

Thanks imnorie!

The itemnumber could be alphanumeric, numeric and may contain N/A or the word Sample. Would that change anything?

awww wow!

I'm way too slow - I should press refresh before posting, esp when I have a tea break in the middle of coding!
It looks like Norie has it under control, both with:
- the tidier use of Join (I had considered it but it's difficult when you forget to transpose the array first!)
- and his better knowledge of SQL (I had no idea about the "In" keyword).

Rob

FirstRow = 3
 Cells(3, 1).Select
lastrow = ActiveCell.End(xlDown).Row     'FIND LAST ROW

 For i = FirstRow To lastrow
   .Open "SELECT * FROM tblItemDescription WHERE ItemNumber = '" & Cells(i, 1) & "'"
  'add code here to place results in cells(i,3) for example : cells(i,3).value = result!
 Next i

printmedia

If it's definitely not a numeric field then just keep the code as it is.

Give it a try and post back if there are any problems.

Norie,

Sorry, I'm being lazy since you're in the driving seat...

How does the ".value" sourced array in your code respond to Items with a leading zero?
Printmedia, could you have any items which begin with a zero?

If the N/A's are errors from formulae, how would you work through them...?
If there are any other variations the Join may lose out to a loop structure.

Just in case you go for a Loop structure, here is some existing code I use for weeding out error values when working with filtering - it may or may not be useful...

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

Select all Open in new window

Rob

Rob

If the values are text it shouldn't matter about leading zeroes.

If the field is numeric and there are leading zeros it still shouldn't matter, 076 = 0076.

Not sure why you ask about errors, I assumed the N/A was in the field of the table being queried not the result of a formula in Excel.

The data in column A could be anything, numbers, text etc.

This excel sheet will be a template used by our product managers. They are given a list of products from the customer and they paste this into column A. If the product is in the table tblItemDescription then it will put the data from the table in the spreadsheet. If the product does not exist in the table (i.e. N/A, sample, ABC etc. anything not in the table) then nothing should appear, the only thing you should see if the the data from the first column.

I have attached a sample of what it should look like
Sample.xlsx

With that example data this is how the SQL will look.

SELECT * FROM tblItemDescription WHERE ItemNumber In('456','N/A','789','ABC','Sampe','111')

The only records that will be returned will be those where the ItemNumber matches the values in the list.

imnorie,

I was using   Sheet1.Range("B3").CopyFromRecordset

to put the data in the spreadsheet, but that's messing up the data (If it can't find the item then instead of leaving the columns blank and going to the next line, it's putting the data from the next item it finds). How do I do it so that it pastes the data row by row, that way if it can't find the item in the table then it'll leave the columns blank and move to the next row.

I have attached the file of what it's doing.
Sample1.xlsx

Ok. I tried your last code and it doesn't start inserting the data on the first item (row 3) but rather it starts populating the data on the row 4.

So:
Item---Description
123---<blank>
456---Description for 123
789---Description for 456

Also, when it reaches an item that doesn't exist in the table or it's N/A or Sample I get the following error:

Run-time error '3201'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

When I debug it, it goes to the following line:
rs.MoveFirst

My mistake.

The rng.Offset(1) should be just before the Wend.

Where do you get the error?

By the way, which column has the ItemNumbers?

You originally said it was column A but in column A the heading is OriginalItemNumber.

The heading in column B is ItemNumber.

So which should it be?

If you but the rst.MoveFirst after the If that should fix the error.

I put the original item number column so you could see what my original line: Sheet1.Range("B3").CopyFromRecordset

was doing. But after your code that goes row by row it's ok now. The item number will still be just in column A.

I moved rng.Offset(1) and rst.MoveFirst and that did it!

Thank you very much imnorie, I really appreciate your help and patience!

No problem.

Glad we finally got it sorted.:)