vba formula output

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

Thanks.

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

Reply | Quote