-1

Hello!

I am trying to build a macro, that finds a blank cell in a range in a single column and sums all cells between this blank cell and the previous blank cell.

I've searched the web a lot, and while others have asked this question, i do not find the answers to them particularly helpful in my situation, as i need this to work through 3500+ rows.

E.g: 

4  
3  
4  
BLANK 1  
2  
5  
7  
1  
BLANK 2  
1  
4  
BLANK 3

In this case the cell called "BLANK 1" would be the sum of the 3 previous rows: 4+3+4=11
"Blank 2" would be 15 and so forth.

The range is "G8:G3561".

Edit

For the quick answer see Mr_Nitrogen's answer. It works beautifully! However, as I am new to VBA and coding in general I do not know how or why the code works.

I am very eager to obtain a better understanding of VBA, which is why I'm continuing this thread (if allowed to).
Furthermore, I would like to provide evidence for the commenters that I have indeed worked on this myself and that I prefer to build my own code.

It's important for me to understand why my code works or doesn't work, which is why i hope that you still want to help me develop my own code.

I finally found an approach that is logical to me. I know that this is not the simplest way to do it, but I would like to know if it could work.

I've written the following code.

Sub Sum_storage()
Dim rng As Range
Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range

Range("G8").End(xlDown).Offset(1, 0).Select
Set cell = Selection
cell.Value = "temp" 'Finds the first blank cell in column G _
                     and creates a temporary value in order _
                     to find the second blank cell

Range("G8").End(xlDown).Offset(1, 0).Select
Set cell2 = Selection

cell.Offset(1, 0).Select
Set cell3 = Selection  'The range i need to sum can _
                        now be described as "cell3:cell2"

Set rng = Range(Range("cell3"), Range("cell2")) 'The code works until this point
cell2.Value = WorksheetFunction.Sum(rng)

The idea is to define the range i want to sum with multiple variables.
My problem is trying to refer to these variables (and setting them in an easier way than using .Offset).

Is it simply not possible to set a range (rng) based on two previously set ranges?

If this is possible the next step for me is creating some kind of loop that could make this work for all 3500+ rows.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
DirtyDeffy
  • 497
  • 7
  • 18
  • 2
    please see [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and don't forget to read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) and [I downvoted because no attempt was made](http://idownvotedbecau.se/noattempt/). Also check out the [tour](https://stackoverflow.com/tour), and more good information at ["help center".](https://stackoverflow.com/help/on-topic) – Foxfire And Burns And Burns Apr 19 '18 at 08:28
  • Welcome to StackOverflow. Please note, that this is not a free code-writing service. Yet, we are eager to help fellow programmers (and aspirants) writing their own code. Please read the help topics on [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask). You might also want to [take the tour](https://stackoverflow.com/tour) and earn a badge while doing so. Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. We will be here waiting for you. Ready to assist and help you finalize *your code*. – Ralph Apr 19 '18 at 08:29
  • If you show some effort in trying to make something, i'll be more then willing to help you fix your code, but this is not a free code service so until i see some real effort. There is plenty of post where you can find the few fuctions you need. Start by finding first empty cell [link](https://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) and continue from there. When you get stuck add your code to the OP and we will assist you to the next step. Good Luck – T. Nesset Apr 19 '18 at 08:48
  • @Ralph Thank you for your comment. I am very sorry that i obviously asked a bad question and missed some etiquette with this one. However, i have put in some effort to try and solve this problem on my own. The reason i haven't provided code examples and such, is because i have a feeling that they take a completely wrong approach. I will work on them a bit more and update the post shortly. It is very important for me to say that i never expected people to provide me a complete code solution. However, reading through the post now, i understand why i led you to believe this. – DirtyDeffy Apr 19 '18 at 09:29
  • @T.Nesset Thank you for your comment. I would like you to read my comment to Ralph, as it is directed to you aswell. The link you gave me is interesting as it gave me and idea, that i will work on. I think the reason i didn't find other answers to similar questions helpful is because they try to give a very specific answer to a specific problem in a specific situation. I need to break the problem down into smaller bits and obtain an understanding of these bits - which your link helps me with. So thank you for that. – DirtyDeffy Apr 19 '18 at 09:33
  • 2
    @JoeBerg Don't worry, even if you are completely up the wrong tree with your approach it doesn't matter. People here will kindly help even on that, as long as the OP shows his effort. – Pᴇʜ Apr 19 '18 at 09:46
  • @JoeBerg as pᴇʜ said even the wrong approach is good to post, and we can give you some pointers to what to improve or how you can re-write it in another approach. People are here to help, but not before some real effort is provided in the op. Even if you cant figure out how to start, link some post you have been looking at to show you tried. Glad the link I sent could give some inspiration, and as I said I'm happy to help if you get stuck.. Every small function that you would need to make this code is on StackOverflow, just search for specific parts of the code, and then merge it together. :) – T. Nesset Apr 19 '18 at 10:41

2 Answers2

3

You were on the right track with using End(xlDown).

This one should be a way faster than looping through all cells, because this jumps to the next empty cell and sums via WorksheetFunction.Sum.

Option Explicit

Public Sub DoMyStuff()
    Dim ws As Worksheet
    Set ws = Worksheets("Tabelle8") 'define your worksheet here

    Dim FirstCell As Range
    Set FirstCell = ws.Range("G8")

    Dim VeryLastCell As Range 'get very last cell as stop criteria
    Set VeryLastCell = ws.Cells(ws.Rows.Count, "G").End(xlUp)

    Do
        Dim LastCell As Range
        If FirstCell.Offset(1) = vbNullString Then 'test if there is only one cell to sum
            Set LastCell = FirstCell
        Else
            Set LastCell = FirstCell.End(xlDown)
        End If

        With LastCell.Offset(1, 0) 'this is the cell we want to write the sum
            .Value = Application.WorksheetFunction.Sum(ws.Range(FirstCell, LastCell))
            .Interior.Color = RGB(255, 0, 0)
        End With

        Set FirstCell = LastCell.Offset(2, 0)

    Loop While FirstCell.Row < VeryLastCell.Row
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Works perfectly aswell - thank you! Unfortunately i can't accept your answer in addition to Mr_Nitrogen's answer. But I'm very grateful that you took the time to post an answer :) – DirtyDeffy Apr 20 '18 at 06:28
  • @JoeBerg That's OK, I'm not hunting points. • If speed matters (eg you have a lot of rows) this would be faster. Eg. for 500 rows this took an average of 90,80 ms where looping through **all** cells took 103,02 ms. So the more rows and the less blank cells you have the faster this code should be in comparison to the other loop. This difference might not matter to you but sometimes it does. Depends on your amount of data. – Pᴇʜ Apr 20 '18 at 06:44
  • I completely agree. I have other codes, where speed is a factor, but in this particular case it is not. It is nice however, to get a better understanding of what takes time when running a code. So thank you :) – DirtyDeffy Apr 20 '18 at 08:39
2

This isnt that complicated to do with for loop, maybe something like

lastrow = Cells(Rows.Count, "G").End(xlUp).Row
firstrow = 8
TempTotal = 0
for x = firstrow to lastrow + 1
    If Cells(x, "G") <> "" Then
        TempTotal = TempTotal + Cells(x, "G")
    Else:   Cells(x, "G") = TempTotal
            Cells(x, "G").Interior.ColorIndex = 4
            TempTotal = 0
    End if
    Next x

Ive made an edit to make it a bit simpler

The Logic of the Code:

  1. Define the last row with data in column "G"
  2. Move down cell by cell until that row
  3. If the cell has a value in it, add it to the temporary total.

  4. If it is blank, inset the temporary total and reset the tempTotal to zero

    1. This is the first line, in which we select the very last last cell in column "G", use use End(xlup) on it to get to the last cell with data and use .row to get the row number of that cell

    2. Set up a For loop, which runs the code between "for" and "next x" lines repeatedly while incrementing the value of x from "firstrow" to "lastrow + 1" each time it repeats ( so if firsrow is 1 and lastrow is 100) then it will run the code 100 times with x = 1,2,3,4,5 etc.)

    3. this is the "if" statement, "<>" means does not equal, so we are saying if the cell on row x, col "G" is not equal to "" which is an empty string (or nothing) then we do the next line (add its value to tempTotal)
    4. if the "If" statement isnt true (if the cell is blank) then we do what is under the "Else" and make that cell equal to TempTotal, change its color to green (4 is a colorcode, they go between 1 and 50), and reset the temptotal to 0.
Jerome Paddick
  • 399
  • 1
  • 14