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

#### Shaolin666

##### New Member
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

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
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.

#### pbornemeier

##### Well-known Member
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``````

#### Shaolin666

##### New Member
That worked pefectly thank you VERY much indeed.

Replies
3
Views
145
Legacy 456155
L
Replies
5
Views
118
Replies
2
Views
53
Replies
0
Views
47
Replies
4
Views
85