IF ISERROR with index match

[aileen]

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]

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.

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

 

[aileen]

Thanks for the help.

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.

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 & ")"

Thanks for the help.

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.

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