using formula in coding

[sindhuja]

Hi,

i want to use the below formula in coding for the till the last used in a sheet.

IF(NOT(ISNA(MATCH(1,($F2=$A$2:$A$21)*($G2>=$B$2:$B$21)*($G2<=$C$2:$C$21),0 ))),INDEX($D$2:$D$21,MATCH(1,($F2=$A$2:$A$21)*($G2>=$B$2:$B$21)*($G2<=$C$2: $C$2 1),0)),"")

can i get assistance

[Bob Phillips]

The ... till the last used ... what?

[sindhuja]

Hi,

Till the last used row in column A of the sheet.

-sindhuja

[Bob Phillips]

You need to set up dynamic named ranges such as column A with a formula of

=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

and
=OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)

etc. and use these names in the formula.

[sindhuja]

I tried with the below coding. The results are not expected.

I guess the error is with the part. RC[-2] is considered as constant. Can you assist to make RC[-2] as dynamic

[vba]
Sub test()
With Worksheets("Sheet1")
r = .Cells(.Rows.Count, "F").End(xlUp).Row
With .Range("i2:i" & r)
.Formula = "=SUMIFS(c[-5],c[-8],RC[-3],C[-7],""<=RC[-2]"",c[-6],"">=RC[-2]"")"
'.Value = .Value
'.Copy
'.PasteSpecial Paste:=xlPasteValues
End With
End With
End Sub
[/vba]

sample sheet attached for reference.

[Bob Phillips]

You must be talking to someone else then, because that has no relation to what I suggested.

[sindhuja]

Tried with the formula given in the coding before trying the one which I gave in the initial post. But doesnot work out for me.