Complex Data Filtering

[mp16]

Dear All,
I have a spreadsheet with the following columns:
Index RF CDS ADJSPRD POSITION
A 220 172 0.077 UP
B 220 142 0.77 UP
C 20 14 0.77 DOWN

What I am aiming to do is follows:
1. Sum up all the RF values in this column, for values above an input "target" currently set at default of 225, my code for this proceedure is :

target = InputBox("Please enter the CDS target level for filter", "CDS Target", 225)
Range("W3").Value = target

Similarly, I will filter out and sum up the corresponding values for the CDS and ADJ SPREAD for all values above 225.

I will then dump this data out into cells - then I will go back and redo this only this time adding the condition that it will have to be either "positive " and > 225 (i.e effectivly remove all "down" data).

My code for this is along the lines:
Sub PreOptimisation()
MsgBox ("Default target set to 225, Option to Change will be given later")
Range("T6").Select
ActiveCell.Formula = "=SUM(G5:G700)/225"

Range("T7").Select
ActiveCell.Formula = "=SUM(H5:H700)/225"



MsgBox "Works so far.... "

End Sub

Sub CheckCDS()
Dim target As Single
target = InputBox("Please enter the CDS target level for filter", "CDS Target", 225)
Range("W3").Value = target
Range("T15").Select


ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-10]C[-12]:R[609]C[-12],"">=225"", R[-10]C[-12]:R[609]C[-12]) /225"

Range("T16").Select






End Sub


Q1. is there a neater way of performing this? using RC11 formatting is complicating.
Q2. How can i use the "target" variable rather than hard code 225 into the script?


Many thanks!
Mp16

[Yesuslave]

Q1. is there a neater way of performing this? using RC11 formatting is complicating.

Unless you have strictly defined columns and rows, the RC definition is the easiest it seems. If you do have strictly defined columns, however, you can do something like

ActiveCell.FormulaR1C1 = _
"=SUMIF(B" & ActiveCell.Row -10 & "B" & ActiveCell.Row + 609 & ">=225"

The benefit of doing it this way is that you can take the "609" and replace it with a variable that you can set to any number you like.

Of course, you could also do this with the Column letter as well, such as
"=SUMIF(" & strCol & ActiveCell.Row - intRowsA & ":" & strCol & ActiveCell.Row + intRowsB)

Here strCol = the column letter you are referring to, and intRowsA would be a variable for how many rows you want to offset up, intRowsB would be how many rows you want to offset down.


Q2. How can i use the "target" variable rather than hard code 225 into the script?

To put the Target variable in, all you need to do is:

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-10]C[-12]:R[609]C[-12]," & ">=" & Target & ", R[-10]C[-12]:R[609]C[-12]) /" & Target


Hope some of this helps,
Joshua Wise

[mp16]

Thank you for your reply.. yes it was helpful. However, I am now trying to do something slightly more cleverer.. (or at least try to).

The Idea is to use the "data" filtering tab to filter out a column of data, say the Top 25, and then to sum this top 25 selection and pass to a variabel for use later. Now I have recorded a macro to do this - rather longwindedly:

Private Sub CommandButton2_Click()

Dim Target As Single
Target = InputBox("Please enter the CDS target level for filter", "CDS Top Target", 225)
Range("W3").Value = Target



Selection.AutoFilter Field:=8, Criteria1:="& Target &, Operator:=xlTop10Items"
Range("H5:H622").Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Asset Optimiser ").Select
'ActiveWindow.ScrollColumn = 6
'ActiveWindow.ScrollColumn = 7
'ActiveWindow.ScrollColumn = 8
'ActiveWindow.ScrollColumn = 9
'ActiveWindow.ScrollColumn = 10
'ActiveWindow.ScrollColumn = 11
'ActiveWindow.ScrollColumn = 12
Range("Y14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(Sheet2!R[-13]C[-24]:R[213]C[-24])/" & Target
Range("Y17").Select
Sheets("Asset Optimiser ").Select

End Sub

As you will see it requires me to cut and paste the "filtered" data to a seperate sheet then to perform a sumation - is there a simpler way to do this?'(if I dont do this it simply sums up the range and not the filtered data set) If so I would be very eager to know!

Many thanks,
Mp16