+ Reply to Thread
Results 1 to 2 of 2

Formula Looks Different but Turns Out Same after VBA Pastes? and C minus 3?

  1. #1
    Registered User
    Join Date
    11-03-2004
    Posts
    75

    Question Formula Looks Different but Turns Out Same after VBA Pastes? and C minus 3?

    Can someone explain how this can be? The vba refs are different, yet once the vba pastes the functions,,, the references are identical!?!?

    In other words, in the first paste into "C2", it refers to: C[-1]:C[1],3,0),
    It appears the function is pin-pointing Col B and Col D by saying: C[-1]:C[1] (c minus 1 column = B) and (c plus 1 col = D)
    When this function pastes itself into "C2" cell, it reflects a range of B:D (which sounds correct).

    The 2nd paste into cell E2 has different references of: C[-3]:C[-1],3,0),
    How can you go backwards 3 times from C? (c minus 1 is B, c minus 2 is a, what would c minus 3 mean?)
    What I don't understand is: how can there be such a thing as [-3] when the start is C?

    Someone shared this with me and I'm trying to understand the function so I can trouble shoot why its returning a "#N/A" value.



    Please Login or Register  to view this content.
    =============================================

    The really mind blowing part to me is -- that once pasted, both come out to be "B:D" even though their references above were different with the C-1 and the C-3, etc..
    Can anyone explain how this/why this is??


    Once formula is pasted, C2 turns out being this:
    =INDIRECT(CONCATENATE("PRODUCTION!",(LEFT(VLOOKUP(TODAY(),'RULE-Table'!B:D,3,0),FIND(",",VLOOKUP(TODAY(),'RULE-Table'!B:D,3,0),1)-1))))

    and E2 turns out being this:
    =INDIRECT(CONCATENATE("PRODUCTION!",(RIGHT(VLOOKUP(TODAY(),'RULE-Table'!B:D,3,0),LEN(VLOOKUP(TODAY(),'RULE-Table'!B:D,3,0))-FIND(",",VLOOKUP(TODAY(),'RULE-Table'!B:D,3,0),1)))))

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Formula Looks Different but Turns Out Same after VBA Pastes? and C minus 3?

    Hi C[-1] means the column which is one column to the left of the cell where you put the formula. in your case C2, so C[-1] refers to column B
    For E2 , C[-3], of course refers to column that is 3 columns to the right, i.e. Column B again...
    Have a look at the absolute and relative references and R1C1 address style
    If you are pleased with a member's answer then use the Star icon to rate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-03-2012, 07:06 PM
  2. Replies: 2
    Last Post: 09-05-2012, 03:20 PM
  3. [SOLVED] Automating a macro that copies and pastes data and pastes 2 rows under the previous one.
    By thebank in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2012, 05:02 PM
  4. I have a script in where it copies a sheet's data, and pastes it, but over pastes...
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2012, 02:20 AM
  5. Formula turns into text after entering in the first value.
    By Tara DAgostino in forum Excel General
    Replies: 1
    Last Post: 01-21-2006, 03:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1