Need some help with codes

[Tom]

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

 

[Zone]

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]

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