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

Hi all.

I'm working on an Excel 2007 template where I have the code below. But this only works for the first row (A3), how do I cange the script to read all the rows in column A (starting on the 3rd row because the first 2 rows are titles etc.). Sometimes there will be one row of data, other times there may be 50. The number of rows will ALWAYS vary.

Any ideas?
Thank you in advance!
.Open "SELECT * FROM tblItemDescription WHERE ItemNumber = '" & Range("A3") & "'"

Open in new window

Who is Participating?
NorieVBA ExpertCommented:
If you want to do it that way then you'll need to have a separate query for each item.
Dim I As Range
Dim rng As range
Set rng = Range("A3")

While rng.Value<>""

        strSQL = SELECT * FROM tblItemDescription WHERE ItemNumber = '" & rng.Value "'"

       .Open strSQL
       Set rng = rng.Offset(1)

       rst.MoveFirst ' change rst to the name of your recordset

      ' check there's a record and if there is put the data in the same row as the item number
      ' across columns starting in column B 
      If Not rst.EOF Then
            For I = 0 to rst.Fields.Count-1
                rng.Offset(,I+1).Value = rst.Fields(I).Value
            Next I
      End If



Open in new window

NorieVBA ExpertCommented:
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

Open in new window

printmediaAuthor Commented:
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?

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

printmediaAuthor Commented:
Would you mind explaining the code so I understand how it works?
NorieVBA ExpertCommented:
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 & ")"

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.

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

    '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

Open in new window

printmediaAuthor Commented:
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).

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
NorieVBA ExpertCommented:

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.

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
        PossibleErrorCodeOfActiveCell = CurrentCell
    End If
End Function

Open in new window

NorieVBA ExpertCommented:

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.

printmediaAuthor Commented:
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
NorieVBA ExpertCommented:
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.
printmediaAuthor Commented:

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.
printmediaAuthor Commented:
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.

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:
NorieVBA ExpertCommented:
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?
NorieVBA ExpertCommented:
If you but the rst.MoveFirst after the If that should fix the error.
printmediaAuthor Commented:
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!
NorieVBA ExpertCommented:
No problem.

Glad we finally got it sorted.:)
Question has a verified solution.

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.

All Courses

From novice to tech pro — start learning today.