g8r777
03-02-2011, 12:48 PM
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
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