Referencing variable cell for auto filter w/o selecting cell & running macro through list

Good Afternoon,
Please pardon the detail of this post but from the content I have read to date, a lack of clarity seems to just **** everyone off and lead to response aversion or time costly responder sub questions.


The following two questions reference the attached COL_OPT spreadsheet and GET_COL macro. I have written a macro that takes data on Swap agreements between specific broker and client combinations, filters collateral that is eligible for each unique combination, and then returns the most optimal choice of instrument to post in morning mark to market operations. The output produces two results, one that is restricted to the data in cells I:H and one that looks at all assets, shown in cells B:G. I have provided dummy data within the sheet for clarity of process. The columns listed are defined as such:

CALL - Mark to market necessary value that must be posted
PORT - Portfolio that the collateral is currently in
CUSIP - CUSIP of the instrument
HCADM - Market value of the instrument after a liquidity adjustment is made per the ISDA master agreement signed
MAT - Maturity of instrument
PAR - Par value available of the instrument

PLEASE NOTE - Per this particular dummy set of data, dont change PNCSWP and 3M i have not calibrated the dummy data to work for any other sets yet, it would take to long. If the user changes cell E124 and F 124 in DATA_CS then the output of the Macro will show the output that justifies the additional loops you see in the macro.

The macro functions properly but I have one coding technique question that I want to understand for more efficient programming technique in the future. Finally I have a question regarding automating the macro to cycle through a list of values and feed all output to a final report sheet. I thank you in advance for your kind advice. Additionally I would appreciate any comment on how I have written this in general as I am new to VBA.

Questions:

1- In the attached sheet and macro GET_COL I have a few instances where the range I need to select for a particular filtering operation is variable, based on a choice selected in a drop down list. In every other instance in my code I was able to prevent any sheet selecting or activation however in the section marked " TWO VARIABLE COLUMN REF" and in the subsequent series of Do While loops I have only had success in operating the macro through selecting or activating cells. It is not essential that I have the macro perform without this feature but I want to know how I can perform these actions without activating or selecting the cells.

2- I want to loop the macro to repeatedly execute through a list of potential CLIENT/BROKERS that I have set in cell R25. I then want to transfer only non empty output resultant from each macro run, fed into cells A3:O19, to be pushed to a final tear away sheet that presents all in one clean list.

I truly appreciate input on how I can most efficiently complete the aforementioned processes.

I will attach the code in a separate post connect to this.



email me at [email protected] and I will send the spreadsheet. It can't get smaller than 1.47mb and that exceeds the limits for attaching it here


Sincerely,
Daniel

CODE: Part 1

Sub GET_COL()
'Written By Daniel Wyczolkowski
'06/01/2012

Application.ScreenUpdating = False




QUERRY.Range("A21:R20000").ClearContents
QUERRY.Range("A5:Q19").ClearContents


SearchClient = QUERRY.Range("CLIENT_").Value
SearchBroker = QUERRY.Range("BROKER_").Value

DATA_.Names.Add Name:="DAT", RefersToR1C1:= _
"=DATA_!R1C1"
DATA_.Range("C1").FormulaR1C1 = "=MATCH(QUERRY!BROKER_,DATA_!R[1],0)"
DATA_.Names.Add Name:="BRKCOL", RefersToR1C1:= _
"=DATA_!R1C3"

brokerCOl = DATA_.Range("BRKCOL").Value
DToday = Range("DAT").Value







DATA_.Range("A2:CC2").AutoFilter
DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=2, Criteria1:=SearchClient
DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=11, Criteria1:= _
">" & DToday + 8, Operator:=xlAnd
DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=brokerCOl, Criteria1:=">0" _
, Operator:=xlAnd
DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=18, Criteria1:=">0" _
, Operator:=xlAnd
DATA_CS.Range("$A$1:$CC$1000").AutoFilter Field:=1, Criteria1:= _
"=" & SearchClient, Operator:=xlAnd
DATA_CS.Range("$A$1:$CC$1000").AutoFilter Field:=3, Criteria1:= _
"=" & SearchBroker, Operator:=xlAnd
DATA_CS.Range("$A$1:$CC$1000").AutoFilter Field:=8, Criteria1:= _
">" & DToday + 8, Operator:=xlAnd


DATA_.Range("C2:D20000").Copy
QUERRY.Range("B20").PasteSpecial Paste:=xlPasteValues
DATA_.Range("R2:R20000").Copy
QUERRY.Range("F20").PasteSpecial Paste:=xlPasteValues
DATA_.Range("K2:K20000").Copy
QUERRY.Range("E20").PasteSpecial Paste:=xlPasteValues
BBQ = brokerCOl + 12





'TWO VARIABLE COLUMN REF
''''''''''''''''''''
Worksheets("DATA_").Select
Cells(3, BBQ).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
QUERRY.Range("D21").PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''Adjustment will be made to simply call upon ranges by broker name in future

Worksheets("DATA_").Select
Cells(3, brokerCOl).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
QUERRY.Range("G21").PasteSpecial Paste:=xlPasteValues
''''''''''''''''''''


DATA_CS.Range("D1:D1000").Copy
QUERRY.Range("J20").PasteSpecial Paste:=xlPasteValues

DATA_CS.Range("U1:U1000").Copy
QUERRY.Range("M20").PasteSpecial Paste:=xlPasteValues

DATA_CS.Range("H1:H1000").Copy
QUERRY.Range("N20").PasteSpecial Paste:=xlPasteValues


QUERRY.Range("D20").FormulaR1C1 = "HCADM"
DATA_CS.Range("K1:K20000").Copy
QUERRY.Range("I20").PasteSpecial Paste:=xlPasteValues

'On Error Resume Next
' QUERRY.Range("HCQ").Copy
'On Error Resume Next
' QUERRY.Range("I21").PasteSpecial xlPasteValues
DATA_CS.Range("E2:F20000").Copy
QUERRY.Range("K21").PasteSpecial Paste:=xlPasteValues

Dim r As Range
Set r = QUERRY.Range("HCQ")

Dim p As Integer
p = r.Height / 15


DATA_.Range("A2:BL20000").AutoFilter

DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=2, Criteria1:=SearchClient _
, Operator:=xlAnd

With QUERRY

.Range("$I$21:$O$1000").Sort Key1:=.Range("K21"), Order1:=xlDescending

End With

callVal = QUERRY.Range("CallVal").Value
Worksheets("QUERRY").Select
Range("L21").Select

For i = 21 To 21 + p - 1
If QUERRY.Cells(i, 11).Value > 0 Then

Do While IsEmpty(ActiveCell) = False





If callVal < ActiveCell.Value Then


ActiveCell.Offset(-16, -2).Formula = "= R2C2"
ActiveCell.Offset(-16, -1).FormulaR1C1 = "=R[16]C[2]"
ActiveCell.Offset(-16, -0).FormulaR1C1 = "=(R[16]C[-1]/R[16]C[0])*R1C10"
ActiveCell.Offset(-16, 15).Formula = "=FLOOR(R[0]C[-15]*.001,1)*1000"
ActiveCell.Offset(-16, 15).Copy
ActiveCell.Offset(-16, -0).PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[16]C[-3]"
ActiveCell.Offset(1, 0).Select
callVal = 0

Else



ActiveCell.Offset(1, 0).Select


End If

Loop
Else
End If
Next

If callVal > 0 Then

For n = 21 To 21 + p - 1



CUSIP = QUERRY.Cells(n, 10)
PORTCU = QUERRY.Cells(n, 13)

If QUERRY.Cells(n, 11).Value < 0 Then

DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=4, Criteria1:="=" & CUSIP _
, Operator:=xlAnd
DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=3, Criteria1:="=" & PORTCU _
, Operator:=xlAnd
DATA_.Range("$A$2:$CC$22000").AutoFilter Field:=11, Criteria1:= _
">" & DToday + 8, Operator:=xlAnd
DATA_.Range("NA").Copy
QUERRY.Cells(n, 11).PasteSpecial xlPasteAll
Worksheets("DATA_").Select
Cells(3, BBQ).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
QUERRY.Cells(n, 12).PasteSpecial Paste:=xlPasteValues


End If



Next

PART 2




With QUERRY

.Range("A21:G1000").Sort Key1:=.Range("G21"), Order1:=xlDescending, Key2:=.Range("E21"), Order2:=xlAscending

End With

With QUERRY

.Range("I21:O1000").Sort Key1:=.Range("I21"), Order1:=xlDescending, Key2:=.Range("O21"), Order1:=xlAscending

End With




callVal = QUERRY.Range("CallVal").Value
callVal_unr = QUERRY.Range("CallVal").Value




Worksheets("QUERRY").Select
Range("L21").Select
Do While IsEmpty(ActiveCell) = False


If callVal < Abs(ActiveCell.Value) Then


ActiveCell.Offset(-16, -2).Formula = "= R2C2"
ActiveCell.Offset(-16, -1).FormulaR1C1 = "=R[16]C[2]"
ActiveCell.Offset(-16, -0).FormulaR1C1 = "=(R[16]C[-1]/R[16]C[0])*R1C10"
ActiveCell.Offset(-16, 15).Formula = "=FLOOR(R[0]C[-15]*.001,1)*1000"
ActiveCell.Offset(-16, 15).Copy
ActiveCell.Offset(-16, -0).PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[16]C[-3]"
ActiveCell.Offset(1, 0).Select
callVal = 0
Else



ActiveCell.Offset(1, 0).Select


End If


Loop

lastact = ActiveCell.Address
Worksheets("QUERRY").Range("F21").Select


Do While callVal > 0


If callVal > ActiveCell.Value Then

ActiveCell.Offset(1, 0).Select

Else

Range(lastact).Activate
ActiveCell.Offset(-16, -11).Formula = "= R2C2"
ActiveCell.Offset(-16, -10).FormulaR1C1 = "=R[16]C[0]"
ActiveCell.Offset(-16, -9).FormulaR1C1 = "=(R[16]C[3]/R[16]C[1])*R1C10"
ActiveCell.Offset(-16, 30).Formula = "=FLOOR(R[0]C[-39]*.001,1)*1000"
ActiveCell.Offset(-16, 30).Copy
ActiveCell.Offset(-16, -9).PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[18]C[-1]"
callVal = 0


End If


Loop


Worksheets("QUERRY").Range("F21").Select


Do While callVal_unr > 0


If callVal_unr > ActiveCell.Value Then

ActiveCell.Offset(1, 0).Select

Else

ActiveCell.Offset(-16, -5).Formula = "= R2C2"
ActiveCell.Offset(-16, -4).FormulaR1C1 = "=R[16]C[0]"
ActiveCell.Offset(-16, -3).FormulaR1C1 = "=(R[16]C[3]/R[16]C[1])*R1C10"
ActiveCell.Offset(-16, 30).Formula = "=FLOOR(R[0]C[-33]*.001,1)*1000"
ActiveCell.Offset(-16, 30).Copy
ActiveCell.Offset(-16, -3).PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[16]C[-1]"
callVal_unr = 0

End If


Loop


Application.ScreenUpdating = True


' FOR NEXT STAGE AUTOMATION DISREGARD FOR NOW ****sourceSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23).EntireRow.Copy _
destinationSheet.Range("1:1")
' RANGE CHANGE ****** destinationSheet.Range("A65536").End(xlUp)(2).EntireRow

Else


End If

End Sub