Macro Question

[MT_Netols]

I have a somewhat lengthy question that I'm hoping someone can help m
with. I can't get a macro I'm trying to write to operate correctly, s
maybe someone knows a code that will help.

Here's the situation:
- I have four sheets (Sheet 1, 2, 3, & 4 for simplicity's sake), Sheet
1-3 contain stocks I'm looking at at various points of my analysi
stage. Sheet 4 contains a list of all the stocks from Sheets 1-3.
- I import the stock quotes on Sheet 4 so I can obtain the most curren
prices for my stocks.
- Now, what I want to do is have my macro run through the list o
stock tickers on Sheet 4, and if it finds the corresponding ticker i
Sheet 1, I want it to copy in the new current price.
- For example: Stock XYZ is on Sheet 1, with the ticker in column
and the current price in column D. The ticker is also on Sheet 4 wit
the new current price in column E, so what I want to do is somethin
along the lines of:
For each A:A in Sheet4! If A = Sheet1! A:A Then
copy Sheet4! E:E into Sheet1! D

I hope what I'm trying to accomplish makes sense, I basically just wan
to update what I have typed in for the current prices without having t
manually type them all in, so if I can have the macro run through th
list in Sheet4, look for it in Sheet1, and if it finds it, copy th
price in. I know that if I can get just one run of this to work I ca
change the Sheet references to have it run for Sheets 1, 2, and 3, s
any help would be greatly appreciated

 

[Die_Another_Day]

Sub UpdateStockPrice()
Dim sVal As Range 'Search Value
Dim tCell As Range 'Ticker Cell
Dim cnt As Long
Dim cnt2 As Long
For cnt = 1 To Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row
Set sVal = Sheets("Sheet4").Range("A" & cnt)
For cnt2 = 1 To 3
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
Next
Next
End Sub

Try that. let me know if you have problems

Charles

 

[Guest]

Sub UpdatePrice()
Dim i As Long, v As Variant, s As String, res As Variant
Dim rng1 As Range, cell1 As Range, rng As Range
Dim rng2 As Range, sh As Worksheet

With Worksheets("Sheet4")
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

v = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(v, 1) To UBound(v, 2)
s = v(i)
Set sh = Worksheets(s)
With sh
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

For Each cell1 In rng1
res = Application.Match(cell1, rng, 0)
If Not IsError(res) Then
Set rng2 = rng(res)
rng2.Offset(0, 3).Value = cell1.Offset(0, 4).Value
End If
Next cell1

Next i
End Sub
obviously test this on a copy of your workbook.

 

[MT_Netols]

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

 

[Die_Another_Day]

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]

Ok, it's all working perfectly. Thanks again for all your help.
don't think I'll have a problem with having a ticker in multiple times
so I think I'll stick with your code for now. If something changes
might attempt a switch. Thanks one more time. :

 

[Guest]

Mine only looks at the first match.

--
Regards,
Tom Ogilvy

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.

 

[Die_Another_Day]

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

Mine only looks at the first match.

 

[Guest]

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.