I am using the following code to find a value in one workbook and return it
to another workbook based on a match between two cells:

Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1],bloombergsymbols.xlsm!C10,0))"

I would like to add to the code that IF an error is returned to enter the
value from the H1 cell from the active workbook. Is this possible? I've
tried many different scenarios but I keep getting an object defined error.
Any help is appreciated. Thanks

You mean H1 from the activeworksheet???


Dim myFormula as string
myFormula = "INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1]," _
& "bloombergsymbols.xlsm!C10,0))"
range("i1").formular1c1 _
= "=if(iserror(" & myformula & "),h1," & myformula & ")"

Untested, uncompiled.




aileen wrote:

I am using the following code to find a value in one workbook and return it
to another workbook based on a match between two cells:

Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1],bloombergsymbols.xlsm!C10,0))"

I would like to add to the code that IF an error is returned to enter the
value from the H1 cell from the active workbook. Is this possible? I've
tried many different scenarios but I keep getting an object defined error.
Any help is appreciated. Thanks

--

Dave Peterson

Thanks for the help.

"Dave Peterson" wrote:

You mean H1 from the activeworksheet???


Dim myFormula as string
myFormula = "INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1]," _
& "bloombergsymbols.xlsm!C10,0))"
range("i1").formular1c1 _
= "=if(iserror(" & myformula & "),h1," & myformula & ")"

Untested, uncompiled.




aileen wrote:

I am using the following code to find a value in one workbook and return it
to another workbook based on a match between two cells:

Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1],bloombergsymbols.xlsm!C10,0))"

I would like to add to the code that IF an error is returned to enter the
value from the H1 cell from the active workbook. Is this possible? I've
tried many different scenarios but I keep getting an object defined error.
Any help is appreciated. Thanks

--

Dave Peterson

H1 should be in R1C1 reference style.

Dim myFormula as string
myFormula = "INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1]," _
& "bloombergsymbols.xlsm!C10,0))"
range("i1").formular1c1 _
= "=if(iserror(" & myformula & "),r1c8," & myformula & ")"



aileen wrote:

Thanks for the help.

"Dave Peterson" wrote:

You mean H1 from the activeworksheet???


Dim myFormula as string
myFormula = "INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1]," _
& "bloombergsymbols.xlsm!C10,0))"
range("i1").formular1c1 _
= "=if(iserror(" & myformula & "),h1," & myformula & ")"

Untested, uncompiled.




aileen wrote:

I am using the following code to find a value in one workbook and return it
to another workbook based on a match between two cells:

Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=INDEX(bloombergsymbols.xlsm!C6,MATCH(RC[-1],bloombergsymbols.xlsm!C10,0))"

I would like to add to the code that IF an error is returned to enter the
value from the H1 cell from the active workbook. Is this possible? I've
tried many different scenarios but I keep getting an object defined error.
Any help is appreciated. Thanks

--

Dave Peterson


--

Dave Peterson