PDA

View Full Version : using formula in coding



sindhuja
05-11-2012, 11:08 PM
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 :help

Bob Phillips
05-12-2012, 12:33 AM
The ... till the last used ... what?

sindhuja
05-13-2012, 08:47 PM
Hi,

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

-sindhuja

Bob Phillips
05-14-2012, 12:12 AM
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
05-14-2012, 10:01 AM
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


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


sample sheet attached for reference.

Bob Phillips
05-14-2012, 10:21 AM
You must be talking to someone else then, because that has no relation to what I suggested.

sindhuja
05-16-2012, 04:52 AM
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.