VBA HW Code.rtf

{\rtf1\ansi\ansicpg1252\cocoartf2576
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\margl1440\margr1440\vieww11520\viewh8400\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0

\f0\fs24 \cf0 Sub VBATest():\
\
    For Each ws In Worksheets\
    \
    Dim NewColumns As Variant\
    Dim Summary_Table_Row As Integer\
    \
    'assuming that we do not know how long the ticker list will be, I wanted to make an array that has an open frame\
   \
    Dim ticker As String\
    Dim yearlybeg As Double\
    Dim yearlyend As Double\
    Dim totalstockvolume As Double\
    Dim stockvolume As Double\
    Dim yearlychange As Double\
    Dim percent As Double\
  \
    totalstockvolume = 0\
    \
    Summary_Table_Row = 2\
\
    NewColumns = Array("Ticker", "Yearly Change", "Percent Change", "Total Stock Volume")\
    'I wanted to ensure there was a space between final column and the new added column\
    LastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column\
     ws.Cells(1, LastColumn + 2).Value = NewColumns(0)\
\
        For i = 1 To 3\
        'the goal was here to reset last column every time so that exact number of columns does not need to be known\
            LastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column\
        '+1 is added to LastColumn so that the previous input is not rewritten\
            ws.Cells(1, LastColumn + 1).Value = NewColumns(i)\
        Next i\
        \
'-----------------------------------------------------------------------------------------------\
        \
      'set yearlybeg for first ticker\
    \
        yearlybeg = ws.Cells(2, 3).Value\
    \
        LastRow_Ticker = ws.Cells(Rows.Count, 1).End(xlUp).Row\
    \
   For i = 2 To LastRow_Ticker\
\
        If ws.Cells(i, 1).Value <> ws.Cells(i + 1, 1).Value Then\
                yearlyend = ws.Cells(i, 6).Value\
                ticker = ws.Cells(i, 1).Value\
\
                'print the ticker in the table\
        \
                ws.Cells(Summary_Table_Row, 9).Value = ticker\
        \
                'now we move onto next column for yearly change\
\
                'checking to see that values are not zero (later on I set the yearly beginning value to ensure that only if the values are 0 throughout, this will be true)\
                    If yearlybeg = 0 Then\
                        yearlychange = 0\
                        percent = 0\
                    Else\
                    yearlychange = yearlyend - yearlybeg\
                    percent = (yearlychange) / yearlybeg\
                    End If\
                   \
            \
                    ws.Cells(Summary_Table_Row, 10).Value = yearlychange\
                        If yearlychange >= 0 Then\
                        ws.Cells(Summary_Table_Row, 10).Interior.ColorIndex = 4\
                        Else\
                        ws.Cells(Summary_Table_Row, 10).Interior.ColorIndex = 3\
                        End If\
                \
                    'now onto next column for percent change\
            \
                    ws.Cells(Summary_Table_Row, 11).Value = percent\
                    ws.Cells(Summary_Table_Row, 11).NumberFormat = "0.00%"\
                    \
                    'need to make sure yearly beg is not zero\
                    yearlybeg = ws.Cells(i + 1, 3).Value\
                        If yearlybeg = 0 Then\
                            yearlybeg = ws.Cells(i + 2, 3)\
                        End If\
            \
                \
                    'now onto next column for total stock volume\
                    'need to add final volume to total\
                    stockvolume = ws.Cells(i, 7).Value\
                    totalstockvolume = totalstockvolume + stockvolume\
                    ws.Cells(Summary_Table_Row, 12).Value = totalstockvolume\
                    'preparing for following data by adding a row\
                    Summary_Table_Row = Summary_Table_Row + 1\
                    'reset total\
                    totalstockvolume = 0\
                    \
            Else\
                stockvolume = ws.Cells(i, 7).Value\
                totalstockvolume = totalstockvolume + stockvolume\
     \
    \
            End If\
        \
        Next i\
         \
    Next ws\
    \
  End Sub}