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 (don't need it to be displayed) 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".

What the program does is extract some specified data from MarketList to
MyList.
My thanks for any help or alternative suggestions.


Tom

 

[Guest]

Hi,

Try to use formula VLOOKUP
or Auto filter but if you want to use VBA to make it done for specified
things, it will be need some criterias you define.

 

[Guest]

try this code

Sub getlist()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set ColBRange = Range(Cells(1, "B"), Cells(LastRow, "B"))

For Each CellA In ColARange

If Not IsEmpty(CellA.Value) Then
Set c = ColBRange.Find(what:=CellA.Value, _
LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox (CellA.Value & _
" was found in column B, row : " & c.Row)
Else
MsgBox ("Did not find " & CellA.Value)
End If
End If

Next CellA

End Sub

 

[Tom]

Thanks for the suggestion. It's ok except that this way it involves daily
work as the codes have to be changed each day because MarketList is a
generic name which could be say, 20070820.csv

Regards,
Tom

 

[Tom]

Visual report of each find is not really necessary in my case, but ok.
However, at this point I want to add a few lines of code for it to copy
several ranges of cell across, and then to continue with the next search. At
which point in your program will I be able to do that?
Also, is it possible to ask the program to ignore the reporting if no match
is found?
Thank you for your help.

Tom

 

[Guest]

Here is the updated code. I added an example of copying ranges when the cell
matches. Not sure from your instruction if copying should bed done only whan
a match is found or in all cases. It is simple enough to move this code to
other places in the loop.

Sub getlist()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set ColBRange = Range(Cells(1, "B"), Cells(LastRow, "B"))
response = vbYes
For Each CellA In ColARange

If Not IsEmpty(CellA.Value) Then
Set c = ColBRange.Find(what:=CellA.Value, _
LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox (CellA.Value & _
" was found in column B, row : " & c.Row)

Range("S" & c.Row & ":Z" & c.Row).Copy _
Destination:=Range("C" & c.Row)
Else
If response = vbYes Then
response = MsgBox("Did not find " & _
CellA.Value & Chr(13) & _
"Do you want to continue reporting unfound cells? :", _
vbYesNo)
End If
End If
End If

Next CellA

End Sub