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 Ive 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 Im 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

Probably better to use FIND or FINDNEXT macro. Then you want the value??
copied where.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JCIrish" wrote in message
...
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 Ive 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 Im 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