Creating Macro to group data

[JW]

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

 

[Jacob Skaria]

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

 

[Otto Moehrbach]

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]

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.

 

[JW]

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?

 

[Otto Moehrbach]

JW

I'm not making much sense of your macro. Maybe I'm just missing
something. Here is the macro:

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 'Column T

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



Note the following comments:

MaxRows = Rows.Count - 1 makes MaxRows equal to 65535

DateTotal = Cells(Rows.Count, 20).Value makes DateTotal equal to the value
of cell T65536.



Let me paraphrase what I think you want to do. Tell me if I'm right or not.

You have dates in Column Q that might or might not repeat.

You have numbers in Column R that you refer to as T/L.

You have a list of unique dates in Column T.

You want to loop through the dates in Column T and sum all the T/L's that
have that date in Column Q, and place that summation in Column U.

Otto

 

[Otto Moehrbach]

JW

Here is a macro that does what I think you want. Try it and let
me know. Otto

Sub ProcessCells()
Dim Total As Double
Dim rColT As Range
Dim rColQ As Range
Dim t As Range
Dim q As Range
Set rColT = Range("T2", Range("T" & Rows.Count).End(xlUp))
Set rColQ = Range("Q2", Range("Q" & Rows.Count).End(xlUp))
For Each t In rColT
Total = 0
For Each q In rColQ
If q.Value = t.Value Then Total = Total + q.Offset(, 1).Value
Next q
t.Offset(, 1) = Total
Next t
End Sub