Hello All.

I have a spreadsheet with data from columns A - P. Each Row has a date and
an associated profit/loss amount. Each row does not have a unique date, so
it is possible that row 2 and 4 may have the same date, for example.

I have rewritten in column Q and R the date (column Q) and P/L (column R).
Column T has the unique Date from the earliest to the latest.

Now, here is my question as my VB writing may be missing some
technicalities. I created a nested For/Next loop to examine column T's date
and compare the dates in Column Q and if it matches, add up the p/L. The
result is to print in column U and I am not getting anything to print. Here
is what I have so far...any guidance is appreciated.

Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If Worksheets("Beta Test Trade Sheet").Cells(Cnt, 17).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 20).Value Then DailyTotal
= DailyTotal + Worksheets("Beta Test Trade Sheet").Cells(Cnt, 18).Value
Next Cnt
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 21).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng - 1, 21).Value + DailyTotal
Next DateRng
End Sub

From VBE menu 'Debug''Step Into' (F8) mode iterate through your code and
check where it is going wrong. I have not changed your code. but believe once
you go through this in F8 mode you will get to know where the problem is///

1. The way you get MaxRows it will return the total number of rows. Do you
want all rows to be checked rows with values///


Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

With Worksheets("Beta Test Trade Sheet")
DailyTotal = 0

'MaxRows = Rows.Count - 1
'The above returns the last used row in Column Q
MaxRows = .Cells(Rows.Count, 17).End(xlUp).Row

DateTotal = .Cells(Rows.Count, 20).Value
'The above refers to the last row in T. Is that right?

For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If .Range("Q" & Cnt) = .Range("T" & DateRng) Then
DailyTotal = DailyTotal + .Range("R" & Cnt).Value
End If
Next Cnt
..Cells(DateRng, 21).Value = .Cells(DateRng - 1, 21).Value + DailyTotal
Next DateRng

End With

End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"JW" wrote:

Hello All.

I have a spreadsheet with data from columns A - P. Each Row has a date and
an associated profit/loss amount. Each row does not have a unique date, so
it is possible that row 2 and 4 may have the same date, for example.

I have rewritten in column Q and R the date (column Q) and P/L (column R).
Column T has the unique Date from the earliest to the latest.

Now, here is my question as my VB writing may be missing some
technicalities. I created a nested For/Next loop to examine column T's date
and compare the dates in Column Q and if it matches, add up the p/L. The
result is to print in column U and I am not getting anything to print. Here
is what I have so far...any guidance is appreciated.

Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If Worksheets("Beta Test Trade Sheet").Cells(Cnt, 17).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 20).Value Then DailyTotal
= DailyTotal + Worksheets("Beta Test Trade Sheet").Cells(Cnt, 18).Value
Next Cnt
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 21).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng - 1, 21).Value + DailyTotal
Next DateRng
End Sub

JW
I cleaned up and simplified your code somewhat. You say that you want
"The result is to print in column U...". Do you mean actually print as by a
printer? I see no print command so I don't think so. Exactly what do you
want to happen under what conditions? Be specific and use generic terms.
No one here works at your office and understands your business. HTH Otto
Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer
DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
With Sheets("Beta Test Trade Sheet")
For Cnt = 2 To MaxRows
If .Cells(Cnt, 17).Value = .Cells(DateRng, 20).Value Then _
DailyTotal = DailyTotal + .Cells(Cnt, 18).Value
Next Cnt
.Cells(DateRng, 21).Value = _
.Cells(DateRng - 1, 21).Value + DailyTotal
End With
Next DateRng
End Sub

"JW" wrote in message
...
Hello All.

I have a spreadsheet with data from columns A - P. Each Row has a date
and
an associated profit/loss amount. Each row does not have a unique date,
so
it is possible that row 2 and 4 may have the same date, for example.

I have rewritten in column Q and R the date (column Q) and P/L (column R).
Column T has the unique Date from the earliest to the latest.

Now, here is my question as my VB writing may be missing some
technicalities. I created a nested For/Next loop to examine column T's
date
and compare the dates in Column Q and if it matches, add up the p/L. The
result is to print in column U and I am not getting anything to print.
Here
is what I have so far...any guidance is appreciated.

Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If Worksheets("Beta Test Trade Sheet").Cells(Cnt, 17).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 20).Value Then
DailyTotal
= DailyTotal + Worksheets("Beta Test Trade Sheet").Cells(Cnt, 18).Value
Next Cnt
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 21).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng - 1, 21).Value +
DailyTotal
Next DateRng
End Sub

Otto,

You are correct and my question was not clear. I would like to have the sum
of the p/l values that pertain to the appropriate dates to appear in column
U...not print to a printer.

For example, each row has date and a p/l. I want this macro to create a new
column T with the first date and last date, then provide a summary of the p/l
for that date that shows as a .value in column U.

I hope that is clearer. I will try out your enhancements below.

"Otto Moehrbach" wrote:

JW
I cleaned up and simplified your code somewhat. You say that you want
"The result is to print in column U...". Do you mean actually print as by a
printer? I see no print command so I don't think so. Exactly what do you
want to happen under what conditions? Be specific and use generic terms.
No one here works at your office and understands your business. HTH Otto
Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer
DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
With Sheets("Beta Test Trade Sheet")
For Cnt = 2 To MaxRows
If .Cells(Cnt, 17).Value = .Cells(DateRng, 20).Value Then _
DailyTotal = DailyTotal + .Cells(Cnt, 18).Value
Next Cnt
.Cells(DateRng, 21).Value = _
.Cells(DateRng - 1, 21).Value + DailyTotal
End With
Next DateRng
End Sub

"JW" wrote in message
...
Hello All.

I have a spreadsheet with data from columns A - P. Each Row has a date
and
an associated profit/loss amount. Each row does not have a unique date,
so
it is possible that row 2 and 4 may have the same date, for example.

I have rewritten in column Q and R the date (column Q) and P/L (column R).
Column T has the unique Date from the earliest to the latest.

Now, here is my question as my VB writing may be missing some
technicalities. I created a nested For/Next loop to examine column T's
date
and compare the dates in Column Q and if it matches, add up the p/L. The
result is to print in column U and I am not getting anything to print.
Here
is what I have so far...any guidance is appreciated.

Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If Worksheets("Beta Test Trade Sheet").Cells(Cnt, 17).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 20).Value Then
DailyTotal
= DailyTotal + Worksheets("Beta Test Trade Sheet").Cells(Cnt, 18).Value
Next Cnt
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 21).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng - 1, 21).Value +
DailyTotal
Next DateRng
End Sub

Ah, thank you Jacob.

When you step through each line, if there is a formula attached, will the
result of the formula show as well?

"Jacob Skaria" wrote:

From VBE menu 'Debug''Step Into' (F8) mode iterate through your code and
check where it is going wrong. I have not changed your code. but believe once
you go through this in F8 mode you will get to know where the problem is///

1. The way you get MaxRows it will return the total number of rows. Do you
want all rows to be checked rows with values///


Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

With Worksheets("Beta Test Trade Sheet")
DailyTotal = 0

'MaxRows = Rows.Count - 1
'The above returns the last used row in Column Q
MaxRows = .Cells(Rows.Count, 17).End(xlUp).Row

DateTotal = .Cells(Rows.Count, 20).Value
'The above refers to the last row in T. Is that right?

For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If .Range("Q" & Cnt) = .Range("T" & DateRng) Then
DailyTotal = DailyTotal + .Range("R" & Cnt).Value
End If
Next Cnt
.Cells(DateRng, 21).Value = .Cells(DateRng - 1, 21).Value + DailyTotal
Next DateRng

End With

End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"JW" wrote:

Hello All.

I have a spreadsheet with data from columns A - P. Each Row has a date and
an associated profit/loss amount. Each row does not have a unique date, so
it is possible that row 2 and 4 may have the same date, for example.

I have rewritten in column Q and R the date (column Q) and P/L (column R).
Column T has the unique Date from the earliest to the latest.

Now, here is my question as my VB writing may be missing some
technicalities. I created a nested For/Next loop to examine column T's date
and compare the dates in Column Q and if it matches, add up the p/L. The
result is to print in column U and I am not getting anything to print. Here
is what I have so far...any guidance is appreciated.

Sub ProcessCells()
Dim Cnt As Long
Dim MaxRows As Long
Dim DailyTotal As Integer

DailyTotal = 0
MaxRows = Rows.Count - 1
DateTotal = Cells(Rows.Count, 20).Value
For DateRng = 2 To DateTotal
For Cnt = 2 To MaxRows
If Worksheets("Beta Test Trade Sheet").Cells(Cnt, 17).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 20).Value Then DailyTotal
= DailyTotal + Worksheets("Beta Test Trade Sheet").Cells(Cnt, 18).Value
Next Cnt
Worksheets("Beta Test Trade Sheet").Cells(DateRng, 21).Value =
Worksheets("Beta Test Trade Sheet").Cells(DateRng - 1, 21).Value + DailyTotal
Next DateRng
End Sub