Solved

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

Posted on 2011-09-28
20
245 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:printmedia
  • 9
  • 7
  • 3
  • +1
20 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36719950
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

0
 

Author Comment

by:printmedia
ID: 36720005
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?

0
 

Author Comment

by:printmedia
ID: 36720011
Would you mind explaining the code so I understand how it works?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36720094
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.
0
 
LVL 10

Expert Comment

by:broro183
ID: 36720171
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

'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

Open in new window


hth
Rob
0
 

Author Comment

by:printmedia
ID: 36720187
Thanks imnorie!

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

Expert Comment

by:broro183
ID: 36720196
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
0
 
LVL 9

Expert Comment

by:experts1
ID: 36720200
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 36720218
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.
0
 
LVL 10

Expert Comment

by:broro183
ID: 36720234
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

Open in new window



Rob
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Expert Comment

by:Norie
ID: 36720268
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.

0
 

Author Comment

by:printmedia
ID: 36720270
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 36720283
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.
0
 

Author Comment

by:printmedia
ID: 36720329
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
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36720373
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

      rst.Close

Wend

Open in new window

0
 

Author Comment

by:printmedia
ID: 36724126
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 36733086
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?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36736450
If you but the rst.MoveFirst after the If that should fix the error.
0
 

Author Comment

by:printmedia
ID: 36743055
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!
0
 
LVL 33

Expert Comment

by:Norie
ID: 36746259
No problem.

Glad we finally got it sorted.:)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now