Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need some help with codes
I currently use an old macro program (.xlm) to extract the closing prices
and their volumes for a list of 20 stock codes from the daily market price list which has thousands of stock codes. I am trying to re-write the same program using VBA which I am beginning to learn and therefore need your help. Let's say the two documents are as named below: MyList MarketList ABC AAB DEF ABC GHI CDEF JKL DEF . . . . . . XYZ ZZZZ The codes I need here a 1. To read "ABC" on MyList then locate its position on the MarketList. 2. After reading "DEF" on MyList, if it then finds "CDEF", it should keep retrying until an exact match is found. 3. The program stops on reading the blank cell after "XYZ". My thanks for any help or alternative suggestions. Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need some help with codes
Tom, there is probably a more efficient way to do this, but this should
work. You don't say what you want to do with the address after finding it, so I just put it in a message box. Assumptions: 1. MyList is in column A of Sheet1 of the workbook containing the code. 2. MyList begins in row 3 (row 1 is the header and row 2 is blank). 3. MarketList is in column A of Sheet1 of a workbook named "MarketList.xls" 4. Both are Excel workbooks and both are open. If this is correct, copy the code below, paste in a regular module in the workbook containing MyList, and run the sub. Hope this helps! James Sub FindinBook2() Dim c As Range, j As Long, This As Variant, Loc As String ThisWorkbook.Worksheets("Sheet1").Activate For j = 3 To Cells(3, "A").End(xlDown).Row This = Cells(j, "A") Set c = Workbooks("MarketList.xls").Worksheets("Sheet1") _ .Columns("A").Find(This, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Loc = "(not found)" Else Loc = c.Address End If MsgBox This & " is in " & Loc Next j End Sub "Tom" wrote in message ... I currently use an old macro program (.xlm) to extract the closing prices and their volumes for a list of 20 stock codes from the daily market price list which has thousands of stock codes. I am trying to re-write the same program using VBA which I am beginning to learn and therefore need your help. Let's say the two documents are as named below: MyList MarketList ABC AAB DEF ABC GHI CDEF JKL DEF . . . . . . XYZ ZZZZ The codes I need here a 1. To read "ABC" on MyList then locate its position on the MarketList. 2. After reading "DEF" on MyList, if it then finds "CDEF", it should keep retrying until an exact match is found. 3. The program stops on reading the blank cell after "XYZ". My thanks for any help or alternative suggestions. Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need some help with codes
"Zone" wrote in message
... Tom, there is probably a more efficient way to do this, but this should work. You don't say what you want to do with the address after finding it, The xlm macro reads the content, "ABC", not the address of "ABC", and stores it in the internal register of the macro sheet. On activating MarketList.xls, the next line of code asks it to find "ABC" and tests to ensure an exact match and not say, "AABC" or "ABCD". It then copies across to MyList the Closing Price and Volume traded on that day for "ABC". After that it goes down to the next code, "DEF". The program then loops and repeats the whole cycle up to "XYZ". When it finds that the last cell is blank, it closes down MarketList and re-positions the cursor at R1C1 and ends. It is a small program for extracting a specific list of information from the MarketList. so I just put it in a message box. This is not needed for now but may be useful to me later on. Many thanks for your suggestions. Tom Assumptions: 1. MyList is in column A of Sheet1 of the workbook containing the code. 2. MyList begins in row 3 (row 1 is the header and row 2 is blank). 3. MarketList is in column A of Sheet1 of a workbook named "MarketList.xls" 4. Both are Excel workbooks and both are open. If this is correct, copy the code below, paste in a regular module in the workbook containing MyList, and run the sub. Hope this helps! James Sub FindinBook2() Dim c As Range, j As Long, This As Variant, Loc As String ThisWorkbook.Worksheets("Sheet1").Activate For j = 3 To Cells(3, "A").End(xlDown).Row This = Cells(j, "A") Set c = Workbooks("MarketList.xls").Worksheets("Sheet1") _ .Columns("A").Find(This, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Loc = "(not found)" Else Loc = c.Address End If MsgBox This & " is in " & Loc Next j End Sub "Tom" wrote in message ... I currently use an old macro program (.xlm) to extract the closing prices and their volumes for a list of 20 stock codes from the daily market price list which has thousands of stock codes. I am trying to re-write the same program using VBA which I am beginning to learn and therefore need your help. Let's say the two documents are as named below: MyList MarketList ABC AAB DEF ABC GHI CDEF JKL DEF . . . . . . XYZ ZZZZ The codes I need here a 1. To read "ABC" on MyList then locate its position on the MarketList. 2. After reading "DEF" on MyList, if it then finds "CDEF", it should keep retrying until an exact match is found. 3. The program stops on reading the blank cell after "XYZ". My thanks for any help or alternative suggestions. Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Codes | Excel Discussion (Misc queries) | |||
Help with codes please!! | Excel Discussion (Misc queries) | |||
ZIP CODES | Excel Worksheet Functions | |||
Zip Codes | Excel Discussion (Misc queries) | |||
Zip Codes | Excel Discussion (Misc queries) |