Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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: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 -- MT_Netol ----------------------------------------------------------------------- MT_Netols's Profile: http://www.excelforum.com/member.php...fo&userid=3766 View this thread: http://www.excelforum.com/showthread.php?threadid=57275 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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 MT_Netols wrote: I have a somewhat lengthy question that I'm hoping someone can help me with. I can't get a macro I'm trying to write to operate correctly, so 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), Sheets 1-3 contain stocks I'm looking at at various points of my analysis 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 current prices for my stocks. - Now, what I want to do is have my macro run through the list of stock tickers on Sheet 4, and if it finds the corresponding ticker in 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 A and the current price in column D. The ticker is also on Sheet 4 with the new current price in column E, so what I want to do is something along the lines of: For each A:A in Sheet4! If A = Sheet1! A:A Then copy Sheet4! E:E into Sheet1! D:D I hope what I'm trying to accomplish makes sense, I basically just want to update what I have typed in for the current prices without having to manually type them all in, so if I can have the macro run through the list in Sheet4, look for it in Sheet1, and if it finds it, copy the price in. I know that if I can get just one run of this to work I can change the Sheet references to have it run for Sheets 1, 2, and 3, so any help would be greatly appreciated. -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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. -- Regards, Tom Ogilvy "MT_Netols" wrote: I have a somewhat lengthy question that I'm hoping someone can help me with. I can't get a macro I'm trying to write to operate correctly, so 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), Sheets 1-3 contain stocks I'm looking at at various points of my analysis 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 current prices for my stocks. - Now, what I want to do is have my macro run through the list of stock tickers on Sheet 4, and if it finds the corresponding ticker in 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 A and the current price in column D. The ticker is also on Sheet 4 with the new current price in column E, so what I want to do is something along the lines of: For each A:A in Sheet4! If A = Sheet1! A:A Then copy Sheet4! E:E into Sheet1! D:D I hope what I'm trying to accomplish makes sense, I basically just want to update what I have typed in for the current prices without having to manually type them all in, so if I can have the macro run through the list in Sheet4, look for it in Sheet1, and if it finds it, copy the price in. I know that if I can get just one run of this to work I can change the Sheet references to have it run for Sheets 1, 2, and 3, so any help would be greatly appreciated. -- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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. : -- MT_Netol ----------------------------------------------------------------------- MT_Netols's Profile: http://www.excelforum.com/member.php...fo&userid=3766 View this thread: http://www.excelforum.com/showthread.php?threadid=57275 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Question | Excel Discussion (Misc queries) | |||
Yet another macro question | Excel Discussion (Misc queries) | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
macro question | Excel Discussion (Misc queries) | |||
Macro Question | Excel Worksheet Functions |