printmedia
asked on
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!
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") & "'"
ASKER
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?
ASKER
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.
Note, if ItemNumber is a numeric field then the single quotes aren't needed.
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 & ")"
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.
hth
Rob
Try this out, you may need to change the number of brackets.
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
hth
Rob
ASKER
Thanks imnorie!
The itemnumber could be alphanumeric, numeric and may contain N/A or the word Sample. Would that change anything?
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
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
Cells(3, 1).Select
lastrow = ActiveCell.End(xlDown).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.
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...
Rob
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
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.
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.
ASKER
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
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.
SELECT * FROM tblItemDescription WHERE ItemNumber In('456','N/A','789','ABC'
The only records that will be returned will be those where the ItemNumber matches the values in the list.
ASKER
imnorie,
I was using Sheet1.Range("B3").CopyFro mRecordset
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
I was using Sheet1.Range("B3").CopyFro
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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?
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.
ASKER
I put the original item number column so you could see what my original line: Sheet1.Range("B3").CopyFro mRecordset
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!
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.:)
Glad we finally got it sorted.:)
Open in new window