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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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


 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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