LOOKUP iterations in VBA 2007

[JCIrish]

In the sub below, LOOKUP returns a value (LookUpAnswer) that is copied to
cell G13 of a different sheet in the workbook. I need to repeat this
procedure 15 times, each time changing the first argument of LOOKUP (“Total A
BndfndIntermedâ€, in the example) and changing as well the cell into which the
return value is copied.

My problem: Nothing I’ve tried has worked to clear the return value of a
given iteration of the code so as to return the correct value on subsequent
iterations. How can I do this?

A related question: I know that my approach is not the most economical or
elegant way of accomplishing what I’m trying to do. Any suggestions for a
better approach that would run through the 15 iterations, each time changing
the LOOKUP argument and the cell range into which the return is copied?

Dim Lookupanswer
Dim LookupRange As Range
Set LookupRange = Worksheets("PortfolioByType").Range("C1:I20")
Lookupanswer = Application.WorksheetFunction _
.Lookup("TOTAL A BndFndIntermd", Worksheets("PortfolioBytype") _
.Range("C1:I120"))


Worksheets("AssetAllocation").Activate
Range("G13").Select
Selection.Value = Lookupanswer
Range("A1").Select