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 |
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 |