Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm having a mind block:

    t.Offset(0, 9).Formula = "=SUMPRODUCT((RC[-15]=R5C[-15]:R" & w & "C[-15])*(RC[-4]<R5C[-4]:R" & w & "C[-4]))+1"
    Produces:
    =SUMPRODUCT((B5=B$5:B$354)*(M5<M$5:M$354))+1
    But I need:
    =SUMPRODUCT((B5=$B$5:$B$354)*(M5<M$5:M$354))+1


    t.Offset(0, 5).FormulaR1C1 = "=VLookup(RC[-5]," & q & ",75,0)"
    Produces:
    =VLOOKUP(A3,'Admin MGR Teams'!$A$2:$BW$200,75,0)
    But I need the result as a value, not formula.

    Help please!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    1. Change

    t.Offset(0, 9).Formula = "=SUMPRODUCT((RC[-15]=R5C[-15]:R" & w & "C[-15])*(RC[-4]<R5C[-4]:R" & w & "C[-4]))+1"

    to

    t.Offset(0, 9).Formula = "=SUMPRODUCT((RC[-15]=R5C2:R" & w & "C2)*(RC[-4]<R5C13:R" & w & "C13))+1"

    2. Below the line

    t.Offset(0, 5).FormulaR1C1 = "=VLookup(RC[-5]," & q & ",75,0)"

    add

    t.Offset(0, 5).Value = t.Offset(0, 5).Value

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.

    I'm sure one day I'll get my head around R1C1.

Posting Permissions

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