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

[Shaolin666]

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

 

[pbornemeier]

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]

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]

I believe this code will do as you asked:

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

 

[Shaolin666]

That worked pefectly thank you VERY much indeed.