www.butterflysystems.co.uk wrote...
....
Upto now I have been using vlookup to find and enter the data in the active
worksheet and then we delete it if not needed. But with a vlookup in 18
columns this can be a pain. Below are some of the 'vlookups' I use, is there
an easy way to put this same functionality into a userform?

'=VLOOKUP(A15,'J:\rev_accs\[CSPost2003.xls]CBNW'!$1:$65536,2,FALSE)'

'=VLOOKUP(A15,'J:\rev_accs\[CSPost2003.xls]CBNW'!$1:$65536,12,FALSE)'

References into closed workbooks result in arrays of the values stored
in those ranges, and Excel seems to cache such arrays, which can cause
problems. So the main thing to do is pull as little as you need from
closed workbooks, and avoid repeated operations on the same data in
closed files.

If your results are 11 columns in order, 2nd through 12th, then use
array formulas to perform the lookup once. For example, if you pulled
these columns into B15:L15 for the values in the other workbook
corresponding to the value in A15, use the array formula

B15:L15:
=VLOOKUP(A15,'J:\rev_accs\[CSPost2003.xls]CBNW'!$1:$65536,
{2,3,4,5,6,7,8,9,10,11,12},0)

This reduces the processing, but still pulls in LOTS of data. That
could be reduced by restricting your lookup table to just the columns
needed,

B15:L15:
=VLOOKUP(A15,'J:\rev_accs\[CSPost2003.xls]CBNW'!$A:$L,
{2,3,4,5,6,7,8,9,10,11,12},0)

Even that could be reduced by using another cell to hold a MATCH call.

M15:
=MATCH(A15,'J:\rev_accs\[CSPost2003.xls]CBNW'!$A:$A,0)

B15:L15:
=IF(M15<=16384,INDEX('J:\rev_accs\[CSPost2003.xls]CBNW'!$B$1:$L$16384,M15,0),
IF(M15<=32768,INDEX('J:\rev_accs\[CSPost2003.xls]CBNW'!$B$16385:$L$32768,M15,0),
IF(M15<=49152,INDEX('J:\rev_accs\[CSPost2003.xls]CBNW'!$B$32769:$L$49152,M15,0),
INDEX('J:\rev_accs\[CSPost2003.xls]CBNW'!$B$49153:$L$65536,M15,0))))