Danny
06-01-2009, 09:32 PM
Greetings,
I am trying to write something that would loop through a range and check if the value in B2 (supplier #) corresponds with the value in A2 (part#) then B3 (supplier #) with A3 (part#) , and so on (basically a vlookup). Then if the values were entered incorrectly it would highlight the entire row. The information in the worksheet would be checked against a table in an access DB with over 200,000 records, but the worksheet would only have around 300 rows.
I am able to run the code to check 1 row, but I am unsure of how to do this in a loop. I am also unable to compare the cell value to the value returned by the query, I am currently pasting the value into a cell (G2), and comparing B2 with G2.
The attached file has the access TABLE pasted into the 2nd sheet, the code w/o a loop in module1 and the code w/ loop in module 2 (as well as below).
Any help or ideas would be greatly appreciated, as well as any pointers on how to do this more efficiently. For my egos sake, I hope these are very complex problems that stump even the gurus for awhile, but I am guessing these are both fairly simple fixes.
Sub CheckValueWithLoop()
Dim wb As Workbook
Dim sh1 As Worksheet
Dim MyConnect As String
Dim MyRecordset As Recordset
Dim SQL As String
Dim c As Range
Dim part As Range
Dim Connection As ADODB.Connection
Set wb = ThisWorkbook
Set sh1 = wb.Worksheets(1)
MyConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Users\Owner\Desktop\ADO.mdb;" + _
"Persist Security Info=False"
Set MyRecordset = New ADODB.Recordset
Set part = sh1.Range(sh1.Range("A2"), sh1.Range("A2").End(xlDown))
For Each c In part
SQL = "SELECT supplier " & _
"FROM Table1 " & _
"WHERE part = " & c.Value
MyRecordset.Open SQL, MyConnect, adOpenStatic, adLockReadOnly 'Code fails here needs to rerun query every time code loops
ActiveSheet.Range("G2").CopyFromRecordset MyRecordset ' remove this line completly
If Not c.Offset(0, 1).Value = Range("G2").Value Then ' Use the value returned from query
c.EntireRow.Interior.ColorIndex = 4
End If
Range("G2").ClearContents
Next c
End Sub
I am trying to write something that would loop through a range and check if the value in B2 (supplier #) corresponds with the value in A2 (part#) then B3 (supplier #) with A3 (part#) , and so on (basically a vlookup). Then if the values were entered incorrectly it would highlight the entire row. The information in the worksheet would be checked against a table in an access DB with over 200,000 records, but the worksheet would only have around 300 rows.
I am able to run the code to check 1 row, but I am unsure of how to do this in a loop. I am also unable to compare the cell value to the value returned by the query, I am currently pasting the value into a cell (G2), and comparing B2 with G2.
The attached file has the access TABLE pasted into the 2nd sheet, the code w/o a loop in module1 and the code w/ loop in module 2 (as well as below).
Any help or ideas would be greatly appreciated, as well as any pointers on how to do this more efficiently. For my egos sake, I hope these are very complex problems that stump even the gurus for awhile, but I am guessing these are both fairly simple fixes.
Sub CheckValueWithLoop()
Dim wb As Workbook
Dim sh1 As Worksheet
Dim MyConnect As String
Dim MyRecordset As Recordset
Dim SQL As String
Dim c As Range
Dim part As Range
Dim Connection As ADODB.Connection
Set wb = ThisWorkbook
Set sh1 = wb.Worksheets(1)
MyConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Users\Owner\Desktop\ADO.mdb;" + _
"Persist Security Info=False"
Set MyRecordset = New ADODB.Recordset
Set part = sh1.Range(sh1.Range("A2"), sh1.Range("A2").End(xlDown))
For Each c In part
SQL = "SELECT supplier " & _
"FROM Table1 " & _
"WHERE part = " & c.Value
MyRecordset.Open SQL, MyConnect, adOpenStatic, adLockReadOnly 'Code fails here needs to rerun query every time code loops
ActiveSheet.Range("G2").CopyFromRecordset MyRecordset ' remove this line completly
If Not c.Offset(0, 1).Value = Range("G2").Value Then ' Use the value returned from query
c.EntireRow.Interior.ColorIndex = 4
End If
Range("G2").ClearContents
Next c
End Sub