Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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

0
printmedia
Asked:
printmedia
  • 9
  • 7
  • 3
  • +1
1 Solution
 
NorieData ProcessorCommented:
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
 
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?

0
 
printmediaAuthor Commented:
Would you mind explaining the code so I understand how it works?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NorieData ProcessorCommented:
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
 
broro183Commented:
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
 
printmediaAuthor Commented:
Thanks imnorie!

The itemnumber could be alphanumeric, numeric and may contain N/A or the word Sample. Would that change anything?
0
 
broro183Commented:
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
 
experts1Commented:
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
 
NorieData ProcessorCommented:
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
 
broro183Commented:
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
 
NorieData ProcessorCommented:
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
 
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
Sample.xlsx
0
 
NorieData ProcessorCommented:
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
 
printmediaAuthor Commented:
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
 
NorieData ProcessorCommented:
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
 
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.

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
 
NorieData ProcessorCommented:
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
 
NorieData ProcessorCommented:
If you but the rst.MoveFirst after the If that should fix the error.
0
 
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!
0
 
NorieData ProcessorCommented:
No problem.

Glad we finally got it sorted.:)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now