I had previously posted on this forum to try and get some help with the following problem but I didnt really get any meaningful help... I have refined the code but I would greatly appreciate any assistance - complicated message but trying to be clear as possible.
Basically what I am saying is that (data will all be sorted in order first):
Check the value of a cell and if the cell in the row directly below has the same value (e.g. check value of cell f2 and f3). If the values are the same, take the sum of corresponding cells in the same row but a different predetermined column (say l2 and l3 for illustrative purposes). Then check the value of the row below cell f3 (f4) (i.e. loop out with offset). If still the same as the values in f2 and f3, add the value of l4 to the running total of l2 and l3. If the cell below (now f5), is not the same as the value in f4, move to the next row and forget the value of the cells l2,l3,l4 and redo the process. Every time the value in f changes, we simply move to the next row.
When the net value of cells in column l = 0, we have found a set of cells which all have the same values in column f (i.e. f2, f3, f4), and the total of cells l2,l3,l4 = 0. OR if the value in cells column l <0 (when checking to see if the cells in the rows below, f column are no longer the same), cut and paste the entire rows concerned into a new sheet.
Code:
Option Explicit
Sub NetISINs()
Dim OutRow As Long
Dim OutCol As Long
Dim NetValue As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
OutRow = 0
OutCol = 0
Do Until IsEmpty(Range("f2").Offset(OutRow, OutCol)) And _
Range("f2").Offset(OutRow, OutCol).Value <> _
Range("f2").Offset(OutRow + 1, OutCol).Value And _
NetValue = 0 Or NetValue < 0
'Question 1: so i want to do until either the range f = empty, i.e.
'no more dta in the sheet so stop the macro.
'But I also wna specify the netting of column l and the cutting of
'rows where the net values = 0 or less than 1,
'so i have tried putting in a if below
If Range("f2").Offset(OutRow, OutCol).Value = _
Range("f2").Offset(OutRow + 1, OutCol).Value Then _
NetValue = Range("f2").Offset(OutRow, 13).Value + _
Range("f2").Offset(OutRow + 1, 13).Value
'How do I cut the cells that fulfil that value?
'How do I move to a different row
OutRow = OutRow + 1
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Apologies for this super long message but again would really appreciate any help....cheers
Last edited by Siddharth Rout; Jul 16th, 2012 at 09:14 AM.
Reason: Fixed Typos + Indented Code For better readability
To loop through column F and find the beginning and end points of a particular value is easy enough, as is summing up corresponding values in another column.
But beyond that, I'm unclear what you really want to do.
if you want some meaningful help, you would need to post a sample workbook, that demonstrates the desired result
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Imagine i have a spreadsheet filled with values on a type of financial product. Column F in the instance above contains the name and date together (using & function) of the financial product. Column L in the instance above contains the quantity of how many of that product where brought and sold in a single transaction. There can be more than one transaction of that product. Below, I have the name of product in normal brackets, the quantity in square.
So I want it to go down each cell in column F, look at the value. If the value (i say value but it is a text value not nummerical) is the same in the row below (i.e. in the three instances of the dfg456 above or the two instances of abc123), sum the values in column L. If the values of column L for all of the same Fs sum to 0, cut and paste the entire rows concerned into a new sheet.
Hope this makes sense?
Then to get a bit more complex, I also want to cut the rows into a new sheet, if the sum of all of the values for the identifical cells in column F are negative.
Hope this makes sense?
Last edited by InderpalHothi; Jul 16th, 2012 at 08:03 AM.
Reason: formatting
Here is a version of that same code with the addition of checking the sum and cutting the rows:
Code:
Sub group_sum()
Dim i As Integer
Dim myVal As String 'value in column F
Dim startRow As Integer
Dim endRow As Integer
Dim groupSum As Integer
startRow = 2
myVal = Range("f2").Value
groupSum = Range("l2").Value
For i = 3 To 9 'in my example I only have 8 records
If Range("f" & i).Value = myVal Then
groupSum = groupSum + Range("l" & i).Value
Else
endRow = i - 1
If groupSum = 0 Then
Range("a" & startRow, "a" & endRow).EntireRow.Cut
'pick a place to paste it, code goes here
End If
startRow = i
groupSum = Range("l" & i).Value
End If
Next i
End Sub
The code you post is great and has given me a lot of insight, but for some reason in the excel example you posted it only cuts he first two rows out and doesnt go on to check the values of the other column fs and the corresponding net values of column L. Any ideas why this is happening?
I think its got something to do with the endrow = i-1
As if we have four same F column values, we would want it to cut and paste all four corresponding rows....
Last edited by InderpalHothi; Jul 16th, 2012 at 11:21 AM.
Reason: format
Added a line in the For loop (had to reset "myVal" for the next group):
Code:
For i = 3 To 9 'in my example I only have 8 records
If Range("f" & i).Value = myVal Then
groupSum = groupSum + Range("l" & i).Value
Else
endRow = i - 1
If groupSum = 0 Then
Range("a" & startRow, "a" & endRow).EntireRow.Cut
'pick a place to paste it, code goes here
End If
startRow = i
myVal = Range("f" & i).Value 'added this ****************
groupSum = Range("l" & i).Value
End If
Next i
[RESOLVED] Excel (looping, netting macro)
Reply With Quote
