Data Analysis in Excel 

Success, in today's ever-changing environment, largely depends on one's capacity for accurate and timely data analysis. 

This is one of Excel's main advantages, particularly when utilizing its programming language VBA (Visual Basic for Applications).

The utilization of variables is a primary benefit of VBA. 

As demonstrated in a recent piece, importing data into variables significantly speeds up analysis when compared to keeping the data in a worksheet range.

This article will advance the strategy by demonstrating how to use variables to combine calculated data with previously imported data and export the entire set back to the spreadsheet. 

The code will be performing a sum but once you will have a good understanding of the code the type of transformation that you van perform on your data are limitless

The variable in which the data will be copied is called Host

Dim Host () as Variant

An additional variable is created  to contain the number of the last used row in column A

Dim lstRow as Long

The full range containing the data needs to be identified

The data sheet is selected and then the last used line in column A is identified

Sheets("Data").select

lstRow = Cells(Rows.Count,"A").End(xlUp).Row

The initial three columns and the rows leading up to the row marked by the variable lstRow will be stored in the variable Host.

Note that the code will import not just the numerical data (located in columns A and B), but also column C. Although column C initially lacks values upon import, it will be utilized to store the result of the sum of columns A and B.

The values of all cells within the designated range (including column C, despite its lack of initial values) are assigned to the variable Host  using the equal sign.

In this way the data and its layout will be perfectly mirrored in the variable

Host = range(cells(1,1),cells(lstRow,3)).value

The analysis part will consist in looping through each row of the variable Host and sum the numbers located in the first 2 columns. The result is placed in the third column. 

A For Loop will be used for this purpose The variable i will be increasing by 1 in order to run through all the rows of the Host variable

Note that the sum is calculated when looping through the variable Host; therefore no reference is made to the worksheet cells.  

For i = 1 To lstRow

' The third column of row i is populated with the sum of values in column A and B at row i

Host(i,3) = Host (i,1) + HOst (i,2)

Next i

At the end of the loop all 3 columns contain numbers, with the third column containing the sum of the first two columns.

Once the loop is over the data is moved from the variable to  the spreadsheet 

Moving the data from the variable to the spreadsheets comprises 3 steps

1. Eliminate the original data from the spreadsheet

2. Copy the data from the variable Host to the spreadsheet 

3. Cancel the variable Host in order to free up space  on your PC

' The range of the sheet from which the data was originally taken is cleared from all the data 

Sheets("Data").Columns("A:C").ClearContents

' Seelct the range where the data from the Host variable will be placed. The range that is selected has the same size of the Host variable 

Range(Cells(1, 1), Cells(lstRow, 3)).Select

' The range in the worksheet is populated with all the values contained in the Host variable 

Selection.Value = Host

' The Host variable is erased to free up space  

Erase Host

The final result is reported in the below picture