Iferror index and match formula in VBA

[A Thayuman]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	QTY		Item Number			Description							Price		Unit		Disc %		Total
2			EL-PL24W0612A			24W LED Panel Light 600x1200mm							$ 30.00
3						#N/A
4						#N/A
5
6
7												The idea of using Index and match in VBA is so that I can overwirte the description when needed without having to worry about deleting the formula.
8	Data Validation Table											Formula is rewirtten when the sheet is opend for use again
9												However when:
10			Item Number		Description
11			EL-PL36W0612A		36W LED Panel Light 600x1200mm							This Code "=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0))" inbeds in the cell I choose, it works fine when activated on opening the sheet by VBA: =INDEX($Z$3:$Z$30,MATCH(C2, $W$3:$W$30, 0))
12			EL-PL24W0612A		24W LED Panel Light 600x1200mm							Howerver when Cell "C" has no information the "F2" shows #N/A
13			EL-PL24W0312A		24W LED Panel Light 300x1200mm
14			EL-PL24W0606A		24W LED Panel Light 600x600mm							This formula pasted as a code in VBA does not work at all "=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0))," ")"
15			EL-PL12W0306A		12W LED Panel Light 300x600mm							And a message window pops up with "Complie Error, Expected: End Statement
16
17
18
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0))
M3:M4	M3	=IFERROR(INDEX($AA$3:$AA$30,MATCH(C3, $W$3:$W$30, 0))," ")
O3:O4	O3	=IFERROR(INDEX($AB$3:$AB$30,MATCH(C3, $W$3:$W$30, 0))," ")
S2:S4	S2	=IFERROR((A2*M2) -(A2*M2*Q2),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C15:D15,C11:D13	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:E4	List	=$C$11:$C$15

 

[James006]

Hi,
In order, as you say, "to paste the formula in VBA"... you do need to use Evaluate
i.e. Evaluate("yourformula")

 

[Fluff]

You would need to double-up on the quotes inside the formula like

"=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")"

 

[A Thayuman]

Hi,
In order, as you say, "to paste the formula in VBA"... you do need to use Evaluate
i.e. Evaluate("yourformula")

Hi Thanks for getting back to me I tried using evaluate as seen below and it does nothing - I get no errors in the VBA message box either

Sub iferror()


Evaluate Range("F2") = "=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")"


End Sub

 

[A Thayuman]

You would need to double-up on the quotes inside the formula like

"=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")"

Range("F2") = "=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")"
I get run time error '1004': application defined n or object defined error

Thanks for your offer to help

 

[James006]

Hi,
You could test:

Sub TestError()
   Range("F2") = Evaluate("=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")")
End Sub

 

[A Thayuman]

Hi,
You could test:

Sub TestError()
   Range("F2") = Evaluate("=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")")
End Sub

When I copied and pasted the code I got #VALUE! not the formula in the cell

Thanks again for helping out, regards Anil

 

[James006]

Apparently you seem to be working with Merged Cells ...

Merged Cells are a total disaster ...!!! and preventing you from working with Excel ...

Just get rid of them ...

 

[Fluff]

Didn't notice you had an extra = sign in the formula, you need to get rid of it

Range("F2").formula="=IFERROR(INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")"

 

[A Thayuman]

Didn't notice you had an extra = sign in the formula, you need to get rid of it

Range("F2").formula="=IFERROR(INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0)),"""")"

Hi Fluff thanks works perfectly thanks for your help