Loop through cells to make formula

S

sehested

New Member
Joined
Jul 1, 2010
Messages
42
I have a problem to loop through a range of cells to make a sum function. My code looks like this:
Code: 
    For i = 15 To months + 15
        Cells(34, i).Formula = "=Sum(" & Cells(36, i) & "," & Cells(36 + poscount, i) & ")"
    Next
I get this error message
run-time error '1004':
Method 'Cells' of object '_Global' failed

Hoe should i loop through the cells to make the sum function?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
I don't see anything goofy. Are 'months' and 'poscount' integers?
 
Upvote 0
This works for the active sheet, presuming the active sheet is a worksheet.
Rich (BB code): 
Sub exa()
Dim i As Long
Const poscount = 3
Const months = 5
    
    For i = 15 To months + 15
        Cells(34, i).Formula = "=Sum(" & Cells(36, i) & "," & Cells(36 + poscount, i) & ")"
    Next
End Sub
 
Upvote 0
Hmmm pretty strange. Your function works in a new workbook, but when i paste it in mine function which is a lot longer i keep getting the same error.
Actual i also found out that the function should be like this
Code: 
For i = 15 To months + 15
        Cells(34, i).Formula = "=Sum(" & Range(Cells(36, i), Cells(36 + poscount, i)) & ")"
    Next
That wont run in you function either :(
 
Upvote 0
I think that if you are using the Range() method here, you'll have to use Range.Address

Cells.Formula = "=SUM(" & Range(Cells,Cells).Address & ")"

In any case, if you are still facing an error, it'll be better if you post your complete code.
 
Upvote 0
The .Address worked in the new workbook but i still get the error in my long function.
I try to post the complete code
Code: 
Sub SortConsumption()
    
    Application.ScreenUpdating = False
    
    Dim months As Integer
    Dim poscount As Integer
    
    Site = Range("F2")
    Division = Range("F3")
    Afdeling = Range("F4")
    Energi = Range("F5")
    
    Range("A36:BB250").ClearContents
    
    If Energi = "" Then
        MsgBox "No Energy form have been selected! Please select Energyform."
    ElseIf Afdeling <> "" Then
        'Kopiere alle målere som er direkte tilknyttet den valgte afdeling.
        ListCount = Worksheets("Måler").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
        poscount = 0
        Worksheets("Forbrug Test").Activate
        For i = 1 To ListCount
            If InStr(Range("I" & i), Afdeling) > 0 And Range("D" & i) = Energi Then
                Rows(i).Select
                Selection.Copy
                Worksheets("Fordeling").Activate
                Range("A" & 36 + poscount).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                poscount = poscount + 1 '
                Worksheets("Forbrug Test").Activate
            End If
        Next
        
        'Kopiere alle børn til de målere som er kommet over første gang.
        'Dette er nødvendigt for at få det reele forbrug på en hovedmåler
        'og sørger for at man ikke kommer til at dele forkert ud.
        For i = 36 To poscount + 36
            Worksheets("Fordeling").Activate
            maaler = Range("E" & i)
            For n = 1 To ListCount
                Worksheets("Forbrug Test").Activate
                If maaler = Range("J" & n) Then
                    'MsgBox "n: " & n & " i: " & i
                    Rows(n).Select
                    Selection.Copy
                    Worksheets("Fordeling").Activate
                    Range("A" & 36 + poscount).Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    poscount = poscount + 1
                End If
            Next
        Next
        
        'Målere der er kommet med flere gange fjernes for at man ikke trækker samme
        'undermåler fra en hovedmåler mere end en gang
        Worksheets("Fordeling").Activate
        For i = 36 To poscount + 36
            maaler = Range("E" & i)
            For m = 1 + i To poscount + 36
                If Range("E" & m) = maaler Then
                    'MsgBox "Dette er m: " & m & " Med måler: " & maaler
                    Rows(m).Select
                    Selection.Delete
                    poscount = poscount - 1
                End If
            Next
        Next
        'Indsætter længden af hovedmålerens navn. Dette gøres fordi når man fratrækker
        'et barn fra en hovedmåler skal man være sikker på der ikke er trukket et barn
        'til undermåleren fra i forvejen da man så vil trække et for lille tal fra hvis
        'man ikke også trak barnets barn fra. Til sidst sorteres de efter længde så forældre
        'altid er højere end deres børn.
        For i = 36 To poscount + 35
            Range("N" & i) = "=Len(E" & i & ")"
        Next
        Range("A36:Z" & poscount + 35).Select
        Selection.Sort Key1:=Range("N36"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
        'Følgende kode sørger for at der ikke er dobbelttælling da child trækkes fra parrents
        'Sørger også for at grafen kan laves ordentligt ved hjælp af months
        Worksheets("Fordeling").Activate
        For i = 36 To 36 + poscount
            If IsError(Application.Match(Range("J" & i).Value, Range("E36:E" & 36 + poscount), 0)) Then
                'MsgBox "Not found "
            Else
                nCol = 15
                months = 0
                matchcount = Application.Match(Range("J" & i).Value, Range("E36:E" & 36 + poscount), 0)
                While Cells(i, nCol) <> ""
                    tempval = Cells(35 + matchcount, nCol)
                    Cells(35 + matchcount, nCol) = tempval - Cells(i, nCol)
                    'MsgBox "nCol:" & nCol & " i: " & i & " tempval: " & tempval
                    nCol = nCol + 1
                    months = months + 1
                Wend
            End If
        Next
        
        'Følgende kode sørger for split ved dem der skal spilttes
        For i = 36 To 36 + poscount
            If Range("K" & i) = "Yes" Then
                key = Range("A" & i)
                Worksheets("Split").Activate
                rowPos = Application.Match(key, Range("A1:A100"), 0)
                If Afdeling = "10023V" Or Afdeling = "10023R" Then
                    colPos = Application.Match(10023, Range("A2:O2"), 0)
                Else
                    colPos = Application.Match(Afdeling, Range("A2:O2"), 0)
                End If
                pFaktor = Cells(rowPos, colPos)
                'MsgBox "Key: " & key & " Afdeling: " & Afdeling & " rowPos: " & rowPos & " colPos: " & colPos & " pFaktor: " & pFaktor
                Worksheets("Fordeling").Activate
                nCol = 15
                While Cells(i, nCol) <> ""
                    Cells(i, nCol) = Cells(i, nCol) * pFaktor
                    'tempval = Cells(35 + matchcount, nCol)
                    'Cells(35 + matchcount, nCol) = tempval - Cells(i, nCol)
                    'MsgBox "nCol:" & nCol & " i: " & i & " tempval: " & tempval
                    nCol = nCol + 1
                Wend
            End If
        Next
        
        'Sørger for at slette målere som ikke er direkte tilknyttet den valgte afdeling
        'Dette gøres da man kan have haft nogen børn over som har skulle trækkes fra en forældre,
        'for at give det rigtige på hovedmåleren, men undermåleren behøver ikke have
        'tilknytning til den valgte afdeling.
        For i = 36 + poscount To 36 Step -1
            If InStr(Range("H" & i), Afdeling) = 0 Then
                Rows(i).Select
                Selection.Delete
                poscount = poscount - 1
            End If
        Next
                
        
        
    ElseIf Division <> "" Then
        poscount = 0
        For i = 1 To 250
            Worksheets("Forbrug Test").Activate
            If InStr(Range("I" & i), Division) > 0 And Range("D" & i) = Energi Then
                Rows(i).Select
                Selection.Copy
                Worksheets("Fordeling").Activate
                Range("A" & 36 + poscount).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                poscount = poscount + 1
            End If
        Next
        Worksheets("Fordeling").Activate
        
        'Slet målere som ikke er tilknyttet en division så de ikke trækkes fra
        'hovedmåleren.
        For i = 36 + poscount To 36 Step -1
            If Range("G" & i) = "" Then
                Rows(i).Select
                Selection.Delete
                poscount = poscount - 1
            End If
        Next
        
        'Følgende kode sørger for at der ikke er dobbelttælling da child trækkes fra parrents
        'Sørger også for at grafen kan laves ordentligt ved hjælp af months
        Worksheets("Fordeling").Activate
        For i = 36 To 36 + poscount
            If IsError(Application.Match(Range("J" & i).Value, Range("E36:E" & 36 + poscount), 0)) Then
                'MsgBox "Not found "
            Else
                nCol = 15
                months = 0
                matchcount = Application.Match(Range("J" & i).Value, Range("E36:E" & 36 + poscount), 0)
                While Cells(i, nCol) <> ""
                    tempval = Cells(35 + matchcount, nCol)
                    Cells(35 + matchcount, nCol) = tempval - Cells(i, nCol)
                    'MsgBox "nCol:" & nCol & " i: " & i & " tempval: " & tempval
                    nCol = nCol + 1
                    months = months + 1
                Wend
            End If
        Next
        
        'Sørger for at slette målere som ikke er direkte tilknyttet den valgte afdeling
        'Dette gøres da man kan have haft nogen børn over som har skulle trækkes fra en forældre,
        'for at give det rigtige på hovedmåleren, men undermåleren behøver ikke have
        'tilknytning til den valgte afdeling.
        For i = 36 + poscount To 36 Step -1
            If InStr(Range("G" & i), Division) = 0 Then
                Rows(i).Select
                Selection.Delete
                poscount = poscount - 1
            End If
        Next
        
    Else
        poscount = 0
        For i = 1 To 250
            Worksheets("Forbrug Test").Activate
            If InStr(Range("F" & i), Site) > 0 And Range("D" & i) = Energi Then
                Rows(i).Select
                Selection.Copy
                Worksheets("Fordeling").Activate
                Range("A" & 36 + poscount).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                poscount = poscount + 1
            End If
        Next
        Worksheets("Fordeling").Activate
        
        'Sørger for ikke at tælle den tommeplads den står på med i poscount når graf skal laves
        poscount = poscount - 1
        
        'Tæller antallet af måneder der skal plottes i grafen senere
        months = WorksheetFunction.CountIf(Range("O36:Z36"), ">0")
    End If
    
    'Delete old chart
    Dim wsItem As Worksheet
    Dim chtObj As ChartObject
    For Each wsItem In ThisWorkbook.Worksheets
        For Each chtObj In wsItem.ChartObjects
            chtObj.Delete
        Next
    Next
    
    'Make chart
    Dim myrange As Range
    
    poscount = poscount + 1
    
    Set myrange = Union(Sheets("Fordeling").Range(Cells(35, 1), Cells(35 + poscount, 1)), Sheets("Fordeling").Range(Cells(35, 15), Cells(35 + poscount, 14 + months)))
      
    Charts.Add
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=myrange, PlotBy:=xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Fordeling"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Opdeling af " & Energi & " i " & Afdeling & " " & Division
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        If Energi = "El" Or Energi = "Fjernvarme" Then
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "kWh"
        Else
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "M3"
        End If
    End With
    
    With ActiveChart.Parent
        .Height = 325 ' resize
        .Width = 300 + 80 * months ' resize
        .Top = 75 ' reposition
        .Left = 100 ' reposition
    End With
    
    For i = 15 To months + 15
        Cells(34, i).Formula = "=Sum(" & Range(Cells(36, i), Cells(36 + poscount, i)).Address & ")"
    Next
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
I'm not very sure about this, but before the SUM part, you have used Charts. I think when you use ActiveChart, it selects the chart.

As soon as you are done with your chart code, you are trying to sum cells, but the chart selection is still active, and so you are getting the error.



It is a good idea to qualify your cells/ranges. This avoids unnecessary errors and confusion.

For example, you could have something like

Dim wsForbrug As Worksheet
Dim wsFordeling As Worksheet

set wsForbrug = Sheets("Forbrug Test")
Set wsFordeling = Sheets("Fordeling")

And then whenever you want to work on the Forbrug Test, just use

wsForburg.Range(...) and so on. Similarly for wsFordeling.

Using this, you don't need to activate/select sheets to work on them.

So, your SUM statement will become (if you are working on Fordeling)

Code: 
    For i = 15 To months + 15
        With wsFordeling
            .Cells(34, i).Formula = "=Sum(" & Range(.Cells(36, i), .Cells(36 + poscount, i)).Address & ")"
        End With
    Next
 
Upvote 0
Thank you sandeep.warrier :) It worked when i put it before the code for the chart.
I will work on the qualifying of cell/ranges in the future :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
#value! error
Replies
6
Views
124
Barny72
B
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
439
rahildhody
R
B
  • Solved
Error on method intersect
Replies
2
Views
122
Beneindias
B
N
  • Solved
For next loop with array and using a variable in range().select
Replies
5
Views
615
notmeiquit
N
L
  • Question Question
radio buttons determining cell values with 2 sets of equations, the first half of the formula works, but second half doesn't
Replies
1
Views
68
Kaper
Kaper

Forum statistics

Threads
1,224,594
Messages
6,179,792
Members
452,942
Latest member
VijayNewtoExcel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top