Ideas Welcome for finding values in external Workbook
 

Hi, i have a workbook that is dependent on values sent to me by
external source. I would like to set up a procedure that would open u
this received book, search for the correct value and then return it t
my worksheet, then return the values to my original spreadsheet, befor
finding the next value to look for!

Can anyone point me in the correct direction? At present my ide
consists of this

find my first value to look for, save it as var1
start loop

open up external spreadsheet,
use find to locate var1,
look at the rows directly beneath var1, create a dynamic range and cop
this,
make original sheet active and paste in relevant cell,
select var 1, drop down to cell directly beneath it, declare as ne
var1
loop until var1 = ""

Is this a suitable path to take? The format of the received SS is no
very user friendly or in the most logical of orders.

If anyone else has created a procedure like this then feel free t
suggest other methods

--
cereldin
-----------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206
View this thread: http://www.excelforum.com/showthread.php?threadid=53085

Ideas Welcome for finding values in external Workbook
 

This pseudo code should give you some ideas:

Dim bk as workbook, sh as worksheet
Dim rng as Range, cell as Range
Dim sh1 as Worksheet, cell1 as Range
set sh = Activesheet
' range where values to be looked up are located
set rng = sh.Range("A1:A10")

set bk = workbooks.Open("C:\My folder\Yourbook.xls")
set sh1 = bk.worksheets(1)

for each cell in rng
set cell1 = sh1.Cells.Find(cell)
if not cell1 is nothing then
' place copied values to right of the lookup value
' in the original sheet (as an example)
cell1.Offset(1,0).Resize(1,5).copy _
destination:=cell.offset(0,1)
end if
Next
bk.close Savechanges:=False

--
Regards,
Tom Ogilvy



"cereldine" wrote:


Hi, i have a workbook that is dependent on values sent to me by a
external source. I would like to set up a procedure that would open up
this received book, search for the correct value and then return it to
my worksheet, then return the values to my original spreadsheet, before
finding the next value to look for!

Can anyone point me in the correct direction? At present my idea
consists of this

find my first value to look for, save it as var1
start loop

open up external spreadsheet,
use find to locate var1,
look at the rows directly beneath var1, create a dynamic range and copy
this,
make original sheet active and paste in relevant cell,
select var 1, drop down to cell directly beneath it, declare as new
var1
loop until var1 = ""

Is this a suitable path to take? The format of the received SS is not
very user friendly or in the most logical of orders.

If anyone else has created a procedure like this then feel free to
suggest other methods!


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=530855