Mine uses the worksheet function Match yours uses the VBA method find. You
loop through the sheet index, I use an array of sheet names. You addressed
all of column A in the sheets 1 to 3 and I added extra code to only look at
the used area (that is just a technical difference - I wouldn't expect it to
be a noticeable difference in performance as I suspect for some commands only
the usedrange is looked at anyway).

Unless the user wants the prices static until he runs the macro, it would be
easy and perhaps preferrable to use Vlookup.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

what's the difference between yours and mine? Also, how hard would it
be to put Lookup's in Sheets 1 to 3 so the macro doesn't have to be ran
continually?

Charles

Tom Ogilvy wrote:
Mine only looks at the first match.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

The whole section needs copied over like this:
Set tCell = Sheets(cnt2).Columns("A").Find _
(sVal, Range("A1"), xlFormulas, xlWhole, xlByRows, _
xlNext, False)
If Not tCell Is Nothing Then
tCell.Offset(0, 3) = sVal.Offset(0, 4)
End If

Set tCell = Sheets(cnt2).Columns("H").Find _
(sVal, Range("H1"), xlFormulas, xlWhole, xlByRows, _
xlNext, False)
If Not tCell Is Nothing Then
tCell.Offset(0, 3) = sVal.Offset(0, 4)
End If

Charles
Note: if any column in sheets 1 - 3 hold more than one instance of the
Ticker you should try Tom's code as mine only finds the first instance.


MT_Netols wrote:
Charles, your code works perfectly, thanks a lot. Not to just have you
cranking out code for me, but I was wondering if it could be modified
slightly. I'd like to break Sheets 1-3 into buys and sells, so the
stocks are split into columns A & H with the prices in D & K. What
would I need to change in the code to make the macro go through one
comparison of Sheet 4 column A to Sheets 1-3 column A, and then again
for Sheet 4 column A to Sheets 1-3 column H? Is it possible to copy
the code in a second time and just change this line:

Set tCell = Sheets(cnt2).Columns("A").Find(sVal, Range("A1"),
xlFormulas, xlWhole, xlByRows, xlNext, False)

to:

Set tCell = Sheets(cnt2).Columns("H").Find(sVal, Range("H1"),
xlFormulas, xlWhole, xlByRows, xlNext, False)

or will that not work?

Thanks for all your help,

Matt


--
MT_Netols
------------------------------------------------------------------------
MT_Netols's Profile: http://www.excelforum.com/member.php...o&userid=37665
View this thread: http://www.excelforum.com/showthread...hreadid=572758