Retrieve value from cell in next to last row of range

[g8r777]

I am trying to write code that will add a line to a named range, place transaction details in the new line and add the total of the new transaction to the running total of all transactions (last column of previous row).

The data for the new transaction comes from UserForm entries but I do not think that is relevant to my question.

I have been able to successfully code the addition of the row and the renaming of the range to now include that row. I have also been able to fill the new row with the new transaction data. My problem is that I cannot get the last column of the new row to equal the last column from the previous row + the new transaction value.

I am having a problem returning, as a value, the last column of the previous row.

Below is the code I have so far.


Dim totalPurchase As Double
Dim PrevRow As Integer
Dim PrevValue As Integer
Dim PrevTotal as Double

Sheets("Transactions").Activate
With Range("Transactions")
.Rows(.Rows.Count + 1).EntireRow.Insert
.Resize(.Rows.Count + 1, .Columns.Count).Name = .Name.Name
End With

With Range("Transactions")
PrevRow = .Rows.Count - 1
PrevValue = .Columns.Count
End With

PrevTotal = Cells(PrevRow, PrevValue)

totalPurchase = numShares.Value * PricePer.Value

NextRow = Range("Transactions").End(xlDown).Row + 1
FirstColumn = Range("Transactions").End(xlToLeft).Column + 1
Cells(NextRow, FirstColumn) = tranDate.Text
Cells(NextRow, FirstColumn + 2) = txTicker.Text
Cells(NextRow, FirstColumn + 4) = sec
Cells(NextRow, FirstColumn + 6) = "Bought " & numShares.Text & " shares at " & PricePer.Value
Cells(NextRow, FirstColumn + 8) = totalPurchase
Cells(NextRow, FirstColumn + 10) = PrevTotal + totalPurchase

This code places a vlue equal to totalPurchase in the +10 column which tells me the code is essentially returning a value of 0 for PrevTotal.

The text in blue are lines of code that I believe are working properly (i.e. they give me the result I expect).

The lines in red are they ones I believe are giving me problems.

As an aside, I tried using similar syntax to the NextRow and FirstColumn code and had even more problems.

For example I tried to define:

PrevRow = Range("Transactions").End(xlDown).Row - 1
LastColumn = Range("Transactions").End(xlToLeft).Column + 10

and defining PrevTotal = Cells(PrevRow, LastColumn)

With this I was able to get a sum but it was totalPurchase + a value from a row well below the current range and in a completely different named range.

I am at a loss. Any help is appreciated.

Thank you,
Brian

[Bob Phillips]

Does that data start in A1?

[g8r777]

The range "Transactions" does not start in A1.

I'm not sure if that matters since everything refers to that range.

The .Rows.Count and .Columns.Count return that correct number of rows and columns in the new range (after the addition of the row and renaming of the range).

I can't figure out why I can't just define PrevTotal as the value that appears in the cell in row#-1 and column# where row# equals the number of rows in the range as given by .Rows.Count and column# equals the number of columns as given by .Columns.Count.

[Bob Phillips]

Oh, believe me, it does.

When you do

Cells(PrevRow, PrevValue)

Where PrevRow is determined from the number of rows, it matters. If the first row is 10 and Rows.Count is 12, you want the value in row 21, but PrevRow will point at 12.

Use



With Range("Transactions")
PrevRow = .Cells(1, 1).Row + .Rows.Count - 2
PrevValue = .Cells(1, 1).Column + .Columns.Count -1
End With

[g8r777]

That didn't work. It just returned a value equal to totalPurchase

[Bob Phillips]

Post your workbook then and let's see the data.

[g8r777]

I have attached the workbook. I have hard coded the column as column 12 and can it to work.

I also have the following issues as well:

I can't get cell L11 to equal J5 + J11. I would like to do this via code since at some point I would like to code for the deletion of trasactions before a certain date.

If I delete all transactions currently (which leaves only one blank row in the range "Transactions") the code will add a new transaction but adds it much farther down the page and out of the range.

I would like cell L15 to equal cell L12 via code as well.