Need VisualBasic help please...

CrashingGirl · 04-23-2008, 08:45 AM

I am certain that this is child's play for most of you, but only having programmed in Perl and SQL (and it's been 3 years) I simply can't figure out how to make this happen: I have one column of discontinuous data in an Excel Spreadsheet. I need to select each cell in a continuous block of data and add the subtotal to the next empty cell. And repeat with each block of data down that column.

Thanks in advance for any help anyone can provide!

ShosMeister · 04-26-2008, 05:38 AM

Try this.

Code:

Public Function SubTotals()
  Dim LastCell As Integer
  Dim lp As Integer
  Dim FirstCell As Integer
  
  LastCell = Selection.SpecialCells(xlCellTypeLastCell).Row
  Range("A1").Select
  For lp = 1 To LastCell + 1
    If Range("A" & lp) = "" And FirstCell > 0 Then
      Range("A" & lp).FormulaR1C1 = "=SUM(R[-" & lp - FirstCell & "] C:R[-1]C)"
      FirstCell = 0
    Else
      If Range("A" & lp) <> "" And FirstCell = 0 Then FirstCell = lp
    End If
  Next lp
End Function

04-23-2008, 08:45 AM	#1 (permalink)
CrashingGirl Registered User Join Date: Apr 2008 Posts: 1 OS: Windows xp	Need VisualBasic help please... I am certain that this is child's play for most of you, but only having programmed in Perl and SQL (and it's been 3 years) I simply can't figure out how to make this happen: I have one column of discontinuous data in an Excel Spreadsheet. I need to select each cell in a continuous block of data and add the subtotal to the next empty cell. And repeat with each block of data down that column. Thanks in advance for any help anyone can provide!