Open another workbook and search for cusips

[Klartigue]

Now its getting a little more complicated.

Is there any way I can write a macro that opens a file in a program called Axys. Then it searches for certain cusips within that file, gets the price, and pastes it into a cell in excel?

For example:

Item Cusip Price EVAL Spread
A1 34281QBU 111.379 ----
cover 34281QBU 111.034
A2 66285WGF 102.397 ----
cover 66285WGF 102.395
A3 70914PPF 110.2 ----
cover 70914PPF 109.798

I want to be able to pull prices from a file in a program called Axys and have the prices pasted into the cells where the --- is.
Before attmepting this, is there a way to write a macro that opens a price file in axys, searches for each of the cusip, finds the eval, and pastes it in the --- space? and from there i would subtract the existing price - eval from axys place it in the spread column.

Thanks!

[Klartigue]

Sub UpdatePricesHydra()
Workbooks.Open Filename:= _
"G:\Charles Luke\Back Office\Price File\pricefile.xlsx"

With ActiveSheet

Dim NumBlocks As Long
Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

If Application.CountIf(.Columns(4), .Cells(i, "D").Value) > 2 Then

.Cells(i, "E").Offset(0, 18).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-19],pricefile.xlsx!C1:C2,2,FALSE)),""vrus"",VLOOKUP(RC[-19],pricefile.xlsx!C1:C2,2,FALSE))"

End If
Next i
End With
End Sub

this is what i have but it wont work becasue o the end with at the bottom?

[Klartigue]

Sub UpdatePricesHydra()
Workbooks.Open Filename:= _
"G:\Charles Luke\Back Office\Price File\pricefile.xlsx"

With ActiveSheet

Dim NumBlocks As Long
Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

If Application.CountIf(.Range("D1").Resize(i), .Cells(i, "D").Value) = 1 Then

.Cells(i, "E").Offset(0, 18).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-19],pricefile.xlsx!C1:C2,2,FALSE)),""vrus"",VLOOKUP(RC[-19],pricefile.xlsx!C1:C2,2,FALSE))"

End If
Next i
End With
End Sub
updated version