Strange Formula Tranformation When Recording A Macro

[adamas]

Hey there, guys!

I'm trying to compile a relatively simple Macros by recording some actions and then adjust the resulting script, but I've stumbled into a particularly weird issue.

So, I'm recording a Macros that pastes a formula into a cell.
- This is the formula I want the macros to paste:

=IF(AND(AND(O7<>"some-text-value",O7<>"some-text-value",O7<>"some-text-value"),OR(W7="some-text-value",W7="some-text-value")),IF(OR(DN7="",DN7=0),"",IF(OR(O7="some-text-value",O7="some-text-value",O7="some-text-value"),"",IF(O7="some-text-value","Hitri","Other"))),"")

Meaning what I do is simply: turn macro recording on, select a cell, and copy/paste the formula above into it. Then stop and I get some VBA code.

- Here's the resulting code, with issue area in bold:

    ActiveCell.FormulaR1C1 = _        "=IF(AND(AND(RC[-106]<>""some-text-value"",RC[-106]<>""some-text-value"",RC[-106]<>""some-text-value""),OR(RC[-98]=""some-text-value"",RC[-98]=""some-text-value"")),IF(OR(RC[-3]="""",RC[-3]=0),"""",IF(OR(RC[-106]=""some-text-value"",RC[-106]=""some-text-value"",RC[-106]=""some-text-value""),"""",IF(RC[-106]=""some-text-value"","[B]"Hitr" & _[/B]
[B]        "her""))),"""")"[/B]

So instead of the original formula I need (issue area in bold):

=IF(AND(AND(O7<>"some-text-value",O7<>"some-text-value",O7<>"some-text-value"),OR(W7="some-text-value",W7="some-text-value")),IF(OR(DN7="",DN7=0),"",IF(OR(O7="some-text-value",O7="some-text-value",O7="some-text-value"),"",IF(O7="some-text-value",[B]"Hitri","Other"))),"")[/B]

I get (issue area in bold):

=IF(AND(AND(O7<>"some-text-value",O7<>"some-text-value",O7<>"some-text-value"),OR(W7="some-text-value",W7="some-text-value")),IF(OR(DN7="",DN7=0),"",IF(OR(O7="some-text-value",O7="some-text-value",O7="some-text-value"),"",IF(O7="some-text-value",[B]"Hitrher"))),"")[/B]

Basically the problem is that when recording the macro Excel doesn't correctly transfer this piece:

"Hitri","Other"))),"")

And on the out I get:

"Hitrher"))),"")

Could anyone be so kind to help me fix the resulting VBA code manually for this to properly match the original formula?




P.S. I did try to manually fix the text by adding missing pieces to both words:

"Hitr[B]i[/B]" & _
       "[B]Ot[/B]her""))),"""")"

BUT I'm still missing the "," part between the two words, and (for the love of god!) I can't figure out how or what to append to do this.

 

[Steph77]

Try changing the bold bit from this:

"",""Hitr" & _
        "her""))),"""")"

To this:

"",""Hitri"",""Other""))),"""")"

 

[ParamRay]

.
.

I think the problem is you need double quotation marks ("") surrounding the text in your formula, i.e. "Other" must be changed to ""Other"".

 

[adamas]

Guys, thanks so much! That line break thing was indeed the issue it seems.

I really do appreciate you both taking the time to look at this! /me tips his imaginary hat