Find last blank cell in a defined range and copy a cumulative total to cell

Shaolin666

New Member
Joined
Apr 3, 2011
Messages
3
Hello,

I wonder if someone could lend me a hand, I am trying to track some variables but having difficulty with the VBA. I have a spreadsheet to track profit margins for items i buy and sell. I will try my best to describe my problem below:

I have starting capital of £200 (Cell C18) but I buy an item for £100 (cell C3), I spend £20 (cell C9) on the item, Its new value is now £150 (Cell C13) so my profit for this item is £30 (Cell C17). These values will change for each subsequent item that follows (I have slider bars to adjust the relevant variables) I would like to keep track of the capital and cumulative profit for 10 items by presing a button after changing the variables for each item and the values for capital and cumulative profit should be copied into 2 columns of range K10:K19 and L10:L19 respectively.

Obviously if i change the variables after the first item then the values change for my previous item hence my button will be pressed at the end of each item and should copy the respective values to the first available empty cells in the ranges K10:K19 and L10:L19. Maybe the table below will describe it a little easier? Note. I am not tracking the profit, just the cumulative profit, i just displayed the profit to describe my objective a little clearer.

Capital Profit Cumulative Profit
1 £200 £30 £30
2 £230 £10 £40
3 £240 £50 £90
4 £290 £35 £125
5
6
7
8
9
10

The button needs to know whether the item is the 1st, 2nd 3rd etc and pull the correct values from the reference cells i mentioned above.

Any help at all with this would be greatly apreciated, no hurry, it is just a little pet project of mine.

Kind regards

Shaolin666
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
Welcome to the Board!

Your text description and the table seem to have little to do with other. Please use Excel Jeanie (see link in my sig) to post a portion of the data in your example, then describe what you want to happen to the data in the posted range.
 

Shaolin666

New Member
Joined
Apr 3, 2011
Messages
3
Thank you for your response, I am not at my computer right now so i cant look at my original spreadsheet so I knocked up a quick replica to illustrate the issue:

Sheet2

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 102px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 122px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Starting Capital</td><td style="text-align: center;">£200</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Capital</td><td>Cumulative Profit</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>Bought For</td><td style="text-align: center;">£100</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">1</td><td style="text-align: center;">£200</td><td style="text-align: center;">£30</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>Spent</td><td style="text-align: center;">£20</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">2</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>New Value</td><td style="text-align: center;">£150</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">3</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>Profit</td><td style="text-align: center;">£30</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">4</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">5</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">6</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">7</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">8</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">9</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align: right;">10</td><td>
</td><td>
</td></tr></tbody></table>

Excel tables to the web >>
Excel Jeanie HTML 4

The slider bars and button didnt show up in the Jeanie tool but you get the idea. Also ignore the cell references i posted in my first post. The ranges here are now I4:I13 and J4:J13 respectively. So, I wish to press the button and have the values £200 and £30 populate the first available blank cells in the 2 ranges, then when i put new values in i want to press the button again and have the new values fall into the next available blank cells in the 2 ranges but I need these and subsequent entires to be cumulative, i.e. if i buy item #2 for £100 again and spend £20 and the new value is £140 hence profit is £20 then Capital entry #2 should be £220 and Cumulative profit entry #2 should be £50 and so on.

Mnay thanks once again for any assistance

Kind regards

Shaolin666


 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
I believe this code will do as you asked:
Code:
Sub CumulativeCopyColCValuesToColIJ()

    Dim lNextReportingRow As Long
    
    lNextReportingRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
    
    If lNextReportingRow = 4 Then
        Cells(lNextReportingRow, 9) = Range("C3").Value
        Cells(lNextReportingRow, 10) = Range("C7").Value
    Else
        Cells(lNextReportingRow, 9) = Range("C7").Value + Cells(lNextReportingRow - 1, 9)
        Cells(lNextReportingRow, 10) = Range("C7").Value + Cells(lNextReportingRow - 1, 10)
    End If
    
    'If you want the input values to be erased after you push the button
    'uncomment the next line
    'Range("C3:C7").Cells.ClearContents
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top