Reconcilation of data using formula array. How to transfer formula via vba codes?

[beginner999]

Hello everyone,

I'm hoping someone can help me reconcile a large amount of information/data while retaining its identifying information. Let me elaborate:

I have the following information/table. Each column contains: = SUM(IF(tDLoNo = $B1, IF(LEFT(tDSys, 5) = "IEDMS", IF(tDYrFlag = "2009Below", tDUAmt, 0), 0), 0))

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>I_DisbU09</td><td>I_DisbU10</td><td>I_DisbU11</td><td>I_DisbU12</td><td>I_DisbU13</td><td>I_DisbU14</td><td>I_DisbU15</td><td>I_DisbU16</td><td>D_DisbU09</td><td>D_DisbU10</td><td>D_DisbU11</td><td>D_DisbU12</td><td>D_DisbU13</td><td>D_DisbU14</td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
</table>


I need the formula thru vba ang get the amount of the certain data. Anyone can help me to transfer the codes? I've been trying for 1 month now and i feel like you guys might be able to help me.

 

[Chris Mack]

What exactly are you trying to do? You want to input the formulas using VBA? Or some kind of calculation with the formula results?

 

[beginner999]

Thank you for the response!

Yes, I want the input formula in VBA codes. Because my current file format, worksheet contain more than array formula. So i might reduce it using VBA than a formula array. Am i right?

 

[Chris Mack]

So you don't want the formulas in the sheet? What are you trying to reconcile?

 

[beginner999]

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>last row</th><th>4876</th></tr>
<tr><td> Loan Number</td><td> </td></tr>
<tr><td>I_LO_NO </td><td>D_LO_NO</td></tr>
<tr><td>PC3CONh-035</td><td>PC-03</td></tr>
<tr><td>04-CFgfF</td><td>04-jkhk</td></tr>
<tr><td>05-CFF</td><td> 05-CFF</td></tr>
<tr><td>07-SYNfgf1001</td><td>07-SYuihk</td></tr>
<tr><td>07-802j222</td><td>07-802kjk222</td></tr>
<tr><td>07-80286mn0</td><td>07-8028fg60</td></tr>
</table>

This is the column(name define) TDLONO in the formula where its equal to $B1.

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>System</th><th> </th><th> TranYrFlag</th></tr>
<tr><td>MS - Reg</td><td> </td><td>2009Below</td></tr>
<tr><td>MS - Reg</td><td> </td><td>Jan-Dec2011</td></tr>
<tr><td>MS - Reg</td><td> </td><td>Jan-Dec2012</td></tr>
</table>

this columns is (TDSYS) and (TDYRFLAG) as you can see in the formula array.

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th> USD Equiv </th></tr>
<tr><td> USDAMT </td></tr>
<tr><td> 3,711,354.07 </td></tr>
<tr><td> 134,446,000.00 </td></tr>
<tr><td> 10,000,000.00 </td></tr>
<tr><td> 16,000,000.00 </td></tr>
<tr><td> 25,000,000.00 </td></tr>
</table>

This is the (TDUAMT)

 

[Chris Mack]

What will your end result look like?

 

[beginner999]

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th><th> - </th></tr>
<tr><td>USD Equiv: Disbursement</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>I_DisbU09</td><td>I_DisbU10</td><td>I_DisbU11</td><td>I_DisbU12</td><td>I_DisbU13</td><td>I_DisbU14</td><td>D_DisbU09</td><td>D_DisbU10</td><td>D_DisbU11</td><td>D_DisbU12</td><td>D_DisbU13</td><td>D_DisbU14</td></tr>
<tr><td> (14,865,506.23)</td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td><td> 15,134,434.61 </td><td> - </td><td> - </td><td> - </td><td> - </td><td> - </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td></td></tr>
</table>


It must be like this. The formula or code will only extract the loan id ane get the USD AMT (TDUAMT) and SUM it based on the data inputted.

 

[Chris Mack]

Thank you for the response!

Yes, I want the input formula in VBA codes. Because my current file format, worksheet contain more than array formula. So i might reduce it using VBA than a formula array. Am i right?

Reduce what?

 

[beginner999]

Reduce of using formula array.

 

[Chris Mack]

Sub Input_Arrays()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = Evaluate("=SUM(IF(tDLoNo=$B" & cell.Row & ",IF(LEFT(tDSys, 5)=""IEDMS"",IF(tDYrFlag=""2009Below"",tDUAmt,0),0),0))")
    Next
End Sub

If you select all of the cells you ordinarily put the array formula into and run this macro, it will input the result of the array formula without putting the actual formula in itself.

Is this what you were looking for?

Chris