Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Hello everyone, My problem is this: I have a large database 10 columns * 40,000 Rows. One of the titles is a code for identifying the other entry's. I often have a different spreadsheet, where i draw information out of the database with the vlookup function. These sheets can consist of up to 10,000 of the above mentioned codes. My problem is that when i write this lookup(vlookup) in VBA code for automation purposes, excel freezes after a minute or so. I have turned screenupdate and calculation off, although upon exit i turn the latter on. Is there anyway around this, like arrays, or is excel simply to slow to perform these operations? Thank you in advance -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Erik,
A sample of the code you use for the vlookup could help. Otherwise, have you tried putting the answer to the lookup into the spreadsheet rather than a formula? eg For each cell in range("B:B") if cells(cell.row,1).value < "" then ' Checks row is used by looking in Col A cell.value = worksheetfunction.vlookup(cells(cell.row,1).value, sheets("Sheet2").range("A1:H999"),2,0) endif next cell I haven't checked but I think my sytax is correct ;) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
sort your database by its identifying code and use approximate match vlookup
(True as the last argument). Even though you have turned off automatic calculation, Excel still calculates formulae when they are entered. If you need to do exact match but can sort the data see http://www.decisionmodels.com/optspe...#Speed_Lookups Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "erikhs" wrote in message ... Hello everyone, My problem is this: I have a large database 10 columns * 40,000 Rows. One of the titles is a code for identifying the other entry's. I often have a different spreadsheet, where i draw information out of the database with the vlookup function. These sheets can consist of up to 10,000 of the above mentioned codes. My problem is that when i write this lookup(vlookup) in VBA code for automation purposes, excel freezes after a minute or so. I have turned screenupdate and calculation off, although upon exit i turn the latter on. Is there anyway around this, like arrays, or is excel simply to slow to perform these operations? Thank you in advance -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Sounds like your requirement is static, so Data=filter=Advanced Filter
would be a good way to move a subset of your data to another sheet. -- Regards, Tom Ogilvy "erikhs" wrote: Hello everyone, My problem is this: I have a large database 10 columns * 40,000 Rows. One of the titles is a code for identifying the other entry's. I often have a different spreadsheet, where i draw information out of the database with the vlookup function. These sheets can consist of up to 10,000 of the above mentioned codes. My problem is that when i write this lookup(vlookup) in VBA code for automation purposes, excel freezes after a minute or so. I have turned screenupdate and calculation off, although upon exit i turn the latter on. Is there anyway around this, like arrays, or is excel simply to slow to perform these operations? Thank you in advance -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Thank you very much for the quick reply. I need to make an excact match as the codes can be very similar, and it is important to get the correct information. The database is frequently updated by others than mysekf, so it is not possible to keep it in the same worksheet. I will perhaps based on you reply, try to import one of the columns most used into a template of a sort and try to sort, and use avlookup perhaps. Are you aware of any array functions that might do the trick. I must add that the lookup is not generally a problem if performed manually, but in VBA it consumes all available memory. -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
It sounds like you should be using a database for this, set-processing is
what they are designed for. MH "erikhs" wrote in message ... Hello everyone, My problem is this: I have a large database 10 columns * 40,000 Rows. One of the titles is a code for identifying the other entry's. I often have a different spreadsheet, where i draw information out of the database with the vlookup function. These sheets can consist of up to 10,000 of the above mentioned codes. My problem is that when i write this lookup(vlookup) in VBA code for automation purposes, excel freezes after a minute or so. I have turned screenupdate and calculation off, although upon exit i turn the latter on. Is there anyway around this, like arrays, or is excel simply to slow to perform these operations? Thank you in advance -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Could you post the chunk of code that does the VLookup?
(I dont see why it should be consuming all available memory, perhaps I have misunderstood what you are doing). Did you look at the method on http://www.decisionmodels.com/optspe...#Speed_Lookups for doing efficient exact match on sorted data? Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "erikhs" wrote in message ... Thank you very much for the quick reply. I need to make an excact match as the codes can be very similar, and it is important to get the correct information. The database is frequently updated by others than mysekf, so it is not possible to keep it in the same worksheet. I will perhaps based on you reply, try to import one of the columns most used into a template of a sort and try to sort, and use avlookup perhaps. Are you aware of any array functions that might do the trick. I must add that the lookup is not generally a problem if performed manually, but in VBA it consumes all available memory. -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
I'd recommend filtering the data with where clauses in your SQL
statement. Don't even use vlookup. Get only the data you need from the database. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Sorry that i have not replied to your suggestions before now. I will be more specific and post chunck of code. I receive from the sender, in this case a financial institution, a list of securities each identified by a code, that can either be a known standard or an inhouse code. For this list of securities i compare the codes to the codes i have in the database that are standardized, with the lookup function. The code could be: USD100100200. Sorting the datbase would be unpractical as i need to be able to determine the excactly which securities have been entered when. Here is the extract: ...Application.Calculation = xlCalculationManual Application.ScreenUpdating = False .... .... Range("m2", Range("m2").End(xlDown)).Name = "MyRange1" Range("MyRange1") = "=VLOOKUP(RC[-1],'[-------database.xls]---BONDS'!C2:C5,3,FALSE)" .... .... Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Thank you again for your time -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
Looks like the database file is not open:
If the database.xls file is not open you will run out of memory because you will have too many external references (and excel will also be extremely slow because it will have to repeatedly read the database file): so make sure you have opened both the xls doing the lookup and the xls (database) that is being looked up. When both files are open the time taken to do an exact match lookup is proportional to the number of rows being scanned before a match is found, which in your case is large. Approximate match uses binary search which would be over 1000 times faster on average for 40000 rows. You dont need to sort the securities list, just the database list. Anyway if the securities list or the database list is not already time-stamped then you could add a sequence number before you sort it, if you need it. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "erikhs" wrote in message ... Sorry that i have not replied to your suggestions before now. I will be more specific and post chunck of code. I receive from the sender, in this case a financial institution, a list of securities each identified by a code, that can either be a known standard or an inhouse code. For this list of securities i compare the codes to the codes i have in the database that are standardized, with the lookup function. The code could be: USD100100200. Sorting the datbase would be unpractical as i need to be able to determine the excactly which securities have been entered when. Here is the extract: ..Application.Calculation = xlCalculationManual Application.ScreenUpdating = False ... ... Range("m2", Range("m2").End(xlDown)).Name = "MyRange1" Range("MyRange1") = "=VLOOKUP(RC[-1],'[-------database.xls]---BONDS'!C2:C5,3,FALSE)" ... ... Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Thank you again for your time -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=526101 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA lookup into large database
You should be able to query the database to match a group of in-hous codes to equivalent standard codes. (I'm assuming you need to do tha as a first step because the rest of the data is organized according t the standard codes.) Running a database query from Excel is relativel easy. You can work with the results directly in a recordset or dum onto a sheet. Bil -- Bill_excelforu ----------------------------------------------------------------------- Bill_excelforum's Profile: http://www.excelforum.com/member.php...fo&userid=3228 View this thread: http://www.excelforum.com/showthread.php?threadid=52610 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Querying Large Database | Excel Discussion (Misc queries) | |||
A way to display key info from a large database? | Excel Discussion (Misc queries) | |||
large database with multiple rows | Excel Worksheet Functions | |||
Navigating a large database? | Excel Discussion (Misc queries) | |||
Pulling data out of a large database | Excel Discussion (Misc queries) |