Iferror + If + Index + Match in VBA

Hi All,

I need help to translate the two formulas below into VBA.

Thanks a lot for your help.

Formula 1:

=IFERROR(D2/(IF(ISNUMBER(INDEX(Tableau!T:T,MATCH(K2,Tableau!R:R,0))), (INDEX(Tableau!T:T,MATCH(K2,Tableau!R:R,0))),INDEX(Tableau!V:V,MATCH(J2,Tableau!Q:Q,0))))-1,"Not in Catwalk")

Formula 2 :

=IFERROR(IF(ISNUMBER(INDEX(Tableau!W:W,MATCH(K2,Tableau!R:R,0))), (INDEX(Tableau!W:W,MATCH(K2,Tableau!R:R,0))),INDEX(Tableau!X:X,MATCH(J2,Tableau!Q:Q,0))),"Not in Catwalk")

Originally Posted by xlnitwit

Hi,

What do you mean by "translate to VBA"? How do you intend to use them and why is a formula not a viable option?

See above.

Originally Posted by Kaper

...And what shall be done with the result?
If it is to be stored in a cell where formula would reside, probably easiest approach is to write there formula and then store as value. Note single quotation marks replaced by double:

Range("A1").formula = "=IFERROR(D2/(IF(ISNUMBER(INDEX(Tableau!T:T,MATCH(K2,Tableau!R:R,0))), (INDEX(Tableau!T:T,MATCH(K2,Tableau!R:R,0))),INDEX(Tableau!V:V,MATCH(J2,Tableau!Q:Q,0))))-1,""Not in Catwalk"")"
Range("A1").value= Range("A1").value

if it is to be stored in variable use
start with something like

dim result_cell as range
set result_cell  = sheets("Tableau").Columns("R:R").Find (what:= Range("K2").value, LookIn:=xlValues, lookat:=xlWhole).offset(0,2) 'offset, because T is 2 columns right from R
if not isnumeric(result_cell) then
  set result_cell  = sheets("Tableau").Columns("Q:Q").Find (what:= Range("J2").value, LookIn:=xlValues, lookat:=xlWhole).offset(0,5) 'offset, because V is 5 columns right from Q
end if
' and here next test probably if result cell is a number or may be if it is not 1 (D2/0 would raise an error too)

edit: first question is of course the same as above. But I started writing a bit earlier, it just took me few minutes to write the code :-)

Thanks a lot. My sheet has hunderds of thousands of rows therefore, vba is a better option rather than writing a formula for each cell.