VBA problem, works fine as formula but not in VBA
 

Hi,

I have a formula that works fine in my spreadsheet but when I try t
record it as a macro it no longer works.

Here's the formula that works:

=IF(IF(H2="40DV",INDEX('THC Table'!E:E,MATCH(C2,'TH
Table'!B:B,0)),IF(H2="40HC",INDEX('THC Table'!H:H,MATCH(C2,'TH
Table'!B:B,0)),IF(H2="20DV",INDEX('THC Table'!D:D,MATCH(C2,'TH
Table'!B:B,0)))))-T2=0,"ok","ERROR")


And here's the VBA code I get when I record the formula as a macro
which doesn't work (seems like the first "IF" statement doesn't ge
recorded):

Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-21]=""40DV"",INDEX('TH
Table'!C[-24],MATCH(RC[-26],'TH
Table'!C[-27],0)),IF(RC[-21]=""40HC"",INDEX('TH
Table'!C[-21],MATCH(RC[-26],'TH
Table'!C[-27],0)),IF(RC[-21]=""20DV"",INDEX('TH
Table'!C[-25],MATCH(RC[-26],'THC Table'!C[-27],0)))))?"
Range("AC2").Select


Please help, thanks.:confused

--
Message posted from http://www.ExcelForum.com

VBA problem, works fine as formula but not in VBA
 

Hi
the recoreded code look o.k (besides the trailing '?')

--
Regards
Frank Kabel
Frankfurt, Germany


Hi,

I have a formula that works fine in my spreadsheet but when I try to
record it as a macro it no longer works.

Here's the formula that works:

=IF(IF(H2="40DV",INDEX('THC Table'!E:E,MATCH(C2,'THC
Table'!B:B,0)),IF(H2="40HC",INDEX('THC Table'!H:H,MATCH(C2,'THC
Table'!B:B,0)),IF(H2="20DV",INDEX('THC Table'!D:D,MATCH(C2,'THC
Table'!B:B,0)))))-T2=0,"ok","ERROR")


And here's the VBA code I get when I record the formula as a macro,
which doesn't work (seems like the first "IF" statement doesn't get
recorded):

Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-21]=""40DV"",INDEX('THC
Table'!C[-24],MATCH(RC[-26],'THC
Table'!C[-27],0)),IF(RC[-21]=""40HC"",INDEX('THC
Table'!C[-21],MATCH(RC[-26],'THC
Table'!C[-27],0)),IF(RC[-21]=""20DV"",INDEX('THC
Table'!C[-25],MATCH(RC[-26],'THC Table'!C[-27],0)))))?"
Range("AC2").Select


Please help, thanks.:confused:


---
Message posted from http://www.ExcelForum.com/

VBA problem, works fine as formula but not in VBA
 

Hi Frank,

Yes the code works fine, BUT, I get a number as a result, not an "ok
or "ERROR" which is what I would like. The trailling '?' is precisel
what I believe is missing from the formula, namely this part of th
formula -T2=0,"ok","ERROR") which goes together with the first par
of the forumla =IF. When I record a macro its seems to ignore it.

Regards,
Xlun

--
Message posted from http://www.ExcelForum.com

VBA problem, works fine as formula but not in VBA
 

Seems like the first IF is missing as well with the T2 + rest. (forgo
it when recording?)

Is this working?

"=IF(IF(RC[-21]=""40DV"",INDEX('THC Table'!C[-24],MATCH(RC[-26],'TH
Table'!C[-27],0)),IF(RC[-21]=""40HC"",INDEX('TH
Table'!C[-21],MATCH(RC[-26],'TH
Table'!C[-27],0)),IF(RC[-21]=""20DV"",INDEX('TH
Table'!C[-25],MATCH(RC[-26],'TH
Table'!C[-27],0)))))-RC[-9]=0,"ok","Error"

--
Message posted from http://www.ExcelForum.com

VBA problem, works fine as formula but not in VBA
 

e18 it works!!!

Thank you both for your help.

Regards,
Xlun

--
Message posted from http://www.ExcelForum.com