Data extraction from a table (VBA)

eebigdog

New Member
Joined
Aug 11, 2013
Messages
20
Hi,
I have some code that compares entries in an Excel SS with entries in an Access DB.
The following code basically works - if a match is found, I get a range in "skuFound".
I can pull out the DB value from "newProdRecords" but I'm having trouble figuring out
how to extract the matched filteredTable value.
Finally, how can I index to other columns in the SS for the matched value row in order to extract
other values?

Thanks for any help provided.

Code:
Set gpsyWs = WB.Gpsy.Worksheets("Gpsy_Rpt")
gpsyWs.Activate

Set wsRange = gpsyWs.Range(Cells(2, 1), Cells(LastRow(gpsyWs, "A"), 1))  

Set filteredTable = CreateTable(tableWS:=gpsyWs, dataRange:=wsRange)
Set filteredTable = CreateTable(fn:="Filter", tableWS:=gpsyWs, dataRange:=wsRange, field1:=1, critr1:="J*")

For dbCounter = 1 To newProdRecords.RecordCount
     Set skuFound = filteredTable.rows.Find(What:=newProdRecords.Fields("Product_ID").Value, LookIn:=xlValues, LookAt:=xlWhole)

     If Not skuFound Is Nothing Then     'got a match!
         MsgBox" SKU in DB is: " & newProdRecords.Fields("Product_ID").Values   '<==OK, gives correct value

         MsgBox" Matching SKU in GPSy is: " & filteredTable.rows.Range(skuFound).Value    '<== Doesn't work!!!
    End If
    newProdRecords.MoveNext
Next dbCounter
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,209
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top