Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
3D sum using VBA
I have a large number of cells on sheet1 (approx 1,300)
which need 3D sums from other sheets in the same book. I have decided to try and do this using code but have been unable to get it to work. Another complication is that a new sheet is added to the file every week. What would be the code for the following and how could it be amended to take into account of the new sheet each week? =SUM('WE 8 4 05:WE 1 4 05'!R4) Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
3D sum using VBA
Gareth,
If you put this in a Module you can use it like a normal worksheet function. You call it like this =SumAcrossSheets(A1,"WE*") and it will return #Value! if one (or more) of the values is not numeric... Public Function SumAcrossSheets(rngCellRef As Range, strID As String) As Long Dim intInc As Integer Application.Volatile For intInc = 1 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(intInc) If .Name Like strID Then Let SumAcrossSheets = SumAcrossSheets + ..Range(rngCellRef.Address) End If End With Next intInc End Function Hth, OJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
3D sum using VBA
Thanks for this but I was hoping to do something like:
Sub totals() Dim cell As Range For Each cell in Range("B4:B30") cell.value = 'whatever the code would be? Next cell End Sub Is this possible? Gareth -----Original Message----- Gareth, If you put this in a Module you can use it like a normal worksheet function. You call it like this =SumAcrossSheets(A1,"WE*") and it will return #Value! if one (or more) of the values is not numeric... Public Function SumAcrossSheets(rngCellRef As Range, strID As String) As Long Dim intInc As Integer Application.Volatile For intInc = 1 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(intInc) If .Name Like strID Then Let SumAcrossSheets = SumAcrossSheets + ..Range(rngCellRef.Address) End If End With Next intInc End Function Hth, OJ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
3D sum using VBA
put in two blank sheets, one named First and one named Last
put all the sheets you want to sum between these two sheets. Sub totals() Dim cell As Range For Each cell in Range("B4:B30") cell.Formula = "=Sum('First:Last'!" & cell.Address & ")" Next cell End Sub If that absolutely isn't acceptable Sub totals() Dim cell As Range, bFirst as Boolean Dim sh as Worksheet Dim sFirst as String, sLast as String bFirst = True for each sh in Thisworkbook.Worksheets if lcase(left(sh.name,2)) = "we" then if bFirst then sFirst = sh.name bFirst = False end if sLast = sh.name end if Next For Each cell in Range("B4:B30") cell.Formula = "=Sum('" & sFirst & ":" & sLast & "'!" & cell.Address & ")" Next cell End Sub -- Regards, Tom Ogilvy "Gareth" wrote in message ... Thanks for this but I was hoping to do something like: Sub totals() Dim cell As Range For Each cell in Range("B4:B30") cell.value = 'whatever the code would be? Next cell End Sub Is this possible? Gareth -----Original Message----- Gareth, If you put this in a Module you can use it like a normal worksheet function. You call it like this =SumAcrossSheets(A1,"WE*") and it will return #Value! if one (or more) of the values is not numeric... Public Function SumAcrossSheets(rngCellRef As Range, strID As String) As Long Dim intInc As Integer Application.Volatile For intInc = 1 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(intInc) If .Name Like strID Then Let SumAcrossSheets = SumAcrossSheets + ..Range(rngCellRef.Address) End If End With Next intInc End Function Hth, OJ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|