Excel VBA Question

[MrAustin]

Hello all,

I am making a tracking sheet for work, and am stumped on this VBA stuff (for now, hehe ) At any rate, here is what is to be accomplished.

There are several column headers, of which there will be placed an AutoFilter on each one. Above the Amount column, I want a button that will sum the contents of the cells in that column (F3:End, lets say). Does anyone here know how to:

1. Figure out the last cell in the column that has data in it with VBA?
2. Sum the figures from F3:LastCell?
3. Pop-up a MsgBox with the Sum in it? (this step I think I can do, but I've no idea how to get the value to be displayed)

I would greatly appreciate any insights into this as I learn this awesome language.

[faulkner132]

Paste this into an Excel macro and run it:
_________________________________

Public Sub Sigma()
    Dim strCol As String
    Dim intRow As Integer
    Dim sngSum As Single
    
    strCol = "A" 'enter the column letter here
    intRow = 3 'enter the row the values start on
    sngSum = 0
    
    'while condition tests if the cell is not empty
    'cell format is A1, A2, A3, etc.
    While Trim(CStr(Range(strCol & intRow).Value)) <> ""
        'cast the value of the cell to a single and add
        sngSum = sngSum + CSng(Range(strCol & intRow).Value)
        intRow = intRow + 1
    Wend
    
    'once we get here, we have processed all the values
    Range(strCol & intRow).Value = sngSum 'display the sum in the cell
    
    'show a message box
    MsgBox sngSum, vbOKOnly + vbInformation, "The sum is"

End Sub