# Thread: How to pull out cards with balances?

1. ## How to pull out cards with balances?

I figured out how to count how many accounts I have open with balances. What I want to do next is start working on a snowball calculator. Before I can do that though I need to pull out to a separate tab the following

Issuing Bank, Card Name. Balance Limit Utilization, Minimum Payment and Amount to goal.

All this information is on the Credit Cards Data tab

My ultimate goal right now is to have the following snowball methods

1) Snowball by Utilization Goal
2) Snowball by balance smallest to largest
3) Snowball by balance largest to smallest
4) Snowball by interest rate lowest to highest
5) Snowball by interest rate highest to lowest

I figured I can break that out by a sort possibly by a radio button to choose a pre-defined sort on the Payment calculation sheet then put the results back to the credit card data sheet. I would use the values on the bank balances tab minus the Fixed Monthly payment tab and what is left will go to the credit card payments

I hope this makes sense as I am tired and my brain is a bit foggy right now.

2. Sorry Emanuel, I'm not quite following this. My brain is also a bit foggy at the moment too though.

I see the Snowball column, but the Payment Calculation Sheet is blank.

Can you be a bit more detailed with the logic you're trying to impart here?

3. Ok I had sleep and can type coherently now.

I *think* the best way to do what I want is to have a vba macro or script "pull"from the credit cards data page the following

Card Name, Util %, % to pay to goal, and minimum payment only for cards with balances

Then after sorting the accounts on this tab based on the sort method I choose have it subtract the total of minimum payments from the total liquid value value.

Then taking the amount left after the minimum payment apply that to the card with balances based on the sort order until each goal is reached and money is left or the payment money for the month runs out.

4. Hi Emanuel,

I just cobbled this together to start the extraction of the info. Tell me if this is along the right direction...

After that, I'd need a bit of clarification on the next step... what sort method are you after?

Code:
```Sub Extract()
Dim ary(3) As Variant
Dim cl As Range
Dim wsSource As Worksheet
Dim wsTarget As Worksheet

Set wsSource = Worksheets("Credit Cards Data")
Set wsTarget = Worksheets("Payment Calculation Sheet")

With wsSource
For Each cl In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
'Check if card balance is > 0
With cl
If .Offset(0, 4).Value > 0 Then
ary(0) = .Offset(0, 2)
ary(1) = .Offset(0, 6)
ary(2) = .Offset(0, 7)
ary(3) = .Offset(0, 9)
With wsTarget
.Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 4) = ary()
End With
End If
End With
Next cl
End With
End Sub```

5. Originally Posted by Ken Puls
Hi Emanuel,

I just cobbled this together to start the extraction of the info. Tell me if this is along the right direction...

After that, I'd need a bit of clarification on the next step... what sort method are you after?

Code:
```Sub Extract()
Dim ary(3) As Variant
Dim cl As Range
Dim wsSource As Worksheet
Dim wsTarget As Worksheet

Set wsSource = Worksheets("Credit Cards Data")
Set wsTarget = Worksheets("Payment Calculation Sheet")

With wsSource
For Each cl In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
'Check if card balance is > 0
With cl
If .Offset(0, 4).Value > 0 Then
ary(0) = .Offset(0, 2)
ary(1) = .Offset(0, 6)
ary(2) = .Offset(0, 7)
ary(3) = .Offset(0, 9)
With wsTarget
.Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 4) = ary()
End With
End If
End With
Next cl
End With
End Sub```
Ken I will try this tonight and i will answer your question tonight

6. Ok when I run it it pulls data but now what I'm looking for entirely just some minor issues.

1) I need the issuing bank along with the card name for it to make sense to me not just the card name
2) The values are not showing in the right format. They are just numeric and not percentage or currency

After manually putting in a header and formatting the cells so they look like

 CARD Percent used Goal to pay to Minimum Payment Platinum 31.60% 29.00% \$28 Costco 1.26% 5.00% \$35.00 Zync 0.00% 0.00% \$33.08 Cash Back 0.15% 0.00% \$8.00 Freedom 34.15% 29.00% Slate 31.47% 28.00% Discover IT 3.61% 0.00% Gas Card 1.44% 0.00% \$9.39 CashRewards 92.95% 90.00% \$298.00 Visa 28.50% 28.00% \$61.00

I want to be able to sort the card by any of the following

Balances both High to Low and Low to High
Interest rate both lowest to highest and highest to lowest.
Utilization percentage both lowest to highest and highest to lowest.

I then want to make a money waterfall and take the money I have left after the minimum payment and apply it to each card until it hits the goal then take anything left and apply it to the next card and so one until either all the cards are at goal or the money is at zero

7. Hi Emanuel,

I've modified the macro to add the formatting, and sort by the Minimum payment first:

Code:
```Sub Extract()
Dim ary(4) As Variant
Dim cl As Range
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim lCol As Long

Set wsSource = Worksheets("Credit Cards Data")
Set wsTarget = Worksheets("Payment Calculation Sheet")

With wsTarget
'Clear out the old data
.Cells.ClearContents

.Range("A1:E1") = Array("Issuing Bank", "Card", "Percent Used", "Goal to pay to", "Minimum Payment")

'Format columns
.Columns("C:D").NumberFormat = "0.00%"
.Columns("E:E").Style = "Currency"
.Columns("A:E").EntireColumn.AutoFit
End With

With wsSource
'Fill table
For Each cl In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
'Check if card balance is > 0
With cl
If .Offset(0, 4).Value > 0 Then
ary(0) = .Offset(0, 0)
ary(1) = .Offset(0, 2)
ary(2) = .Offset(0, 6)
ary(3) = .Offset(0, 7)
ary(4) = .Offset(0, 9)
With wsTarget
.Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 5) = ary()
End With
End If
End With
Next cl
End With

With wsTarget
.Sort.SortFields.Clear
Key:=Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
With .Sort
.SetRange Range("A1:E" & wsTarget.Range("E" & wsTarget.Rows.Count).End(xlUp).Row)
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub```
From here, I'm not quite sure on how to proceed. Since we can only sort by one thing to start with, it would seem to make sense to me that any additional sorts would be manually triggered. As far as the money to apply, where is that, and how do you see it being applied to the schedule, exactly? Are you thinking of a formula that shows ever decreasing amounts available or... Will that money be provided at runtime, is it alreayd somewhere in your file?

8. That code works perfectly.

The money to apply is the total liquid value on the Bank Balances tab in cell F1

I was thinking of having a drop down box with the different sort options and whatever one is chosen is the sort that's applied.

After giving this some thought I think the amount that can be applied to the bills should be the liquid amount after subtracting the fixed monthly bills which is already it's own tab.

The total left in fixed bills is found in cell M4 on the Fixed Monthly Payments Tab the only problems with that tab are

1) It's adding future payments example the car and insurance payments are not due this month but it's adding them in
2) I have no idea how to make it reset that the payment is due when the month changes again without manually removing the value in Amt Paid This Month

See the Red Text on Payment Calculation sheet for my thoughts about the "waterfall"