Hi You are using a slightly wrong one,
try to change R[-7] to R[-6] like below:
=IF(AND(R[-6]C[-5]<>R[-7]C[-5]...... and rest yourself.
On Sat, Mar 31, 2018 at 4:55 AM, Johnny <flem...@gmail.com> wrote:
> Hi All, this is the strangest problem I've ever had with inserting a
> formula into a cell with VBA. This is the formula that I am trying to
> insert into cell K2:
>
> =IF(AND(B2<>B1,B2<>B3),"ONLY",IF(AND(B2=B1,B2<>B3),"LAST",
> IF(B3=B2,TEXT(I3-J2,"h:mm"))))
>
>
> It records like this:
> "=IF(AND(R[-7]C[-5]<>R[-6]C[-5],R[-5]C[-5]<>R[-6]C[-5]),""
> ONLY"",IF(AND(R[-7]C[-5]=R[-6]C[-5],R[-6]C[-5]<>R[-5]C[-5]),
> ""LAST"",IF(R[-5]C[-5]=R[-6]C[-5],TEXT(R[-5]C[2]-R[-6]C[3],""h:mm""))))"
>
>
> If I manually insert the formula into K2, it works perfectly and I can
> fill down, and it works perfectly all the way down the column.
>
> Here's the problem, this is how it ends up in cell K2:
>
> =IF(AND(G1048571<>G1048572,G1048573<>G1048572),"ONLY",IF(
> AND(G1048571=G1048572,G1048572<>G1048573),"LAST",IF(
> G1048573=G1048572,TEXT(N1048573-O1048572,"h:mm"))))
>
> I have never seen anything like this in all the time I've been tinkering
> with VBA. Not only do the Column references get messed up, but what's up
> with the numbers in the hundreds of thousands?
>
> I use the syntax of Range("K2").FormulaR1C1 = ............................
> (and it works with every other formula I've ever tried). There's just
> something funky about this particular formula I'm trying to insert. Please
> help me, I thank you in advance!
>
> John
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Thanks & Regards.
Aas Mohd.
*https://www.youtube.com/watch?v=sfInP8sZ3DQ
<https://www.youtube.com/watch?v=sfInP8sZ3DQ>*
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
