cwb1021
03-15-2017, 06:56 AM
Experts,
Im attempting create a procedure that compares values from multiple columns in a range, and if several conditions are met, performs a simple calculation with offset cells and returns the value to a new column. I'll spell out what I am attempting for each step to try and clarify. I've attached the worksheet as well showing the data set, and the results im hoping to get from the procedure.
This would be 4 If Then statements within a loop with the following objective:
1. If Di = "V" AND Bi = "1225", divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
2. If Di = "V" AND Bi = "875", divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
3. If Di = "C" AND Bi = "875" AND Ci is equal to the cell above it (cell.value of Ci = cell.offset(-1,0), divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
4. If Di = "L" AND Bi = "875" OR "850" AND Ci is equal to the cell above it (cell.value of Ci = cell.offset(-1,0), divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
18652
As an example for step 1 for clarity, if there are 5 rows in the range that have a value of "V" in row D and "1225" in row B, the procedure should sum the values of column G for these 5 rows, divide it by the sum of the values in column H and return the same result for all 5 rows to column I. So if the value of the sum of column G divided by the sum of column H = 45, the value for all 5 rows in column I should be 45, not a running total.
So, I've started with this code below and not sure if im going about this the right way. I'm very new to vba and pretty sure that my syntax is not too great and the part inside the loop is mostly to demonstrate what I'm trying to do.
Sub TestSum()
Dim ws1 As Worksheet
Dim Sect As Range, DiffSet As Range, TimeSum As Range
Dim TimeAvg As Range, cell As Range
Set ws1 = Worksheets("Sheet1")
Set Sect = Intersect(ws1.Columns(4), ws1.UsedRange)
Set DiffSet = Intersect(ws1.Columns(7), ws1.UsedRange)
Set TimeSum = Intersect(ws1.Columns(8), ws1.UsedRange)
TimeAvg = WorksheetFunction.Sum(DiffSet) / WorksheetFunction.Sum(TimeSum)
For Each cell In Sect
If cell.Value = "V" And cell.Offset(-2, 0) = "1225" Then
If cell.Value = "V" And cell.Offset(-2, 0) = "875" Then
If cell.Value = "C" And cell.Offset(-2, 0) = "875" And cell.Offset(-1, 0) = cell.Offset(-1, -1) Then
If cell.Value = "L" And (cell.Offset(-2, 0) = "875" Or cell.Offset(-2, 0) = "850") And cell.Offset(-1, 0) = cell.Offset(-1, -1) Then
cell.Offset(5, 0) = TimeAvg
End If
cell.Offset(5, 0) = TimeAvg
End If
cell.Offset(5, 0) = TimeAvg
End If
cell.Offset(5, 0) = TimeAvg
End If
Next cell
End Sub
I would actually prefer to have done this as a worksheet function, but I wasn't sure how to go about making the arguments as ranges among other things. I've also calculated the column I to show the results im hoping for. I'll use this with a much larger data set later on.
Please let me know if there is any more information that I can or should provide. Any help is greatly appreciated.
Thanks,
Chris
Im attempting create a procedure that compares values from multiple columns in a range, and if several conditions are met, performs a simple calculation with offset cells and returns the value to a new column. I'll spell out what I am attempting for each step to try and clarify. I've attached the worksheet as well showing the data set, and the results im hoping to get from the procedure.
This would be 4 If Then statements within a loop with the following objective:
1. If Di = "V" AND Bi = "1225", divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
2. If Di = "V" AND Bi = "875", divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
3. If Di = "C" AND Bi = "875" AND Ci is equal to the cell above it (cell.value of Ci = cell.offset(-1,0), divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
4. If Di = "L" AND Bi = "875" OR "850" AND Ci is equal to the cell above it (cell.value of Ci = cell.offset(-1,0), divide the sum of the selected rows in column G by the sum of the selected rows in column H. Return value to Column I
18652
As an example for step 1 for clarity, if there are 5 rows in the range that have a value of "V" in row D and "1225" in row B, the procedure should sum the values of column G for these 5 rows, divide it by the sum of the values in column H and return the same result for all 5 rows to column I. So if the value of the sum of column G divided by the sum of column H = 45, the value for all 5 rows in column I should be 45, not a running total.
So, I've started with this code below and not sure if im going about this the right way. I'm very new to vba and pretty sure that my syntax is not too great and the part inside the loop is mostly to demonstrate what I'm trying to do.
Sub TestSum()
Dim ws1 As Worksheet
Dim Sect As Range, DiffSet As Range, TimeSum As Range
Dim TimeAvg As Range, cell As Range
Set ws1 = Worksheets("Sheet1")
Set Sect = Intersect(ws1.Columns(4), ws1.UsedRange)
Set DiffSet = Intersect(ws1.Columns(7), ws1.UsedRange)
Set TimeSum = Intersect(ws1.Columns(8), ws1.UsedRange)
TimeAvg = WorksheetFunction.Sum(DiffSet) / WorksheetFunction.Sum(TimeSum)
For Each cell In Sect
If cell.Value = "V" And cell.Offset(-2, 0) = "1225" Then
If cell.Value = "V" And cell.Offset(-2, 0) = "875" Then
If cell.Value = "C" And cell.Offset(-2, 0) = "875" And cell.Offset(-1, 0) = cell.Offset(-1, -1) Then
If cell.Value = "L" And (cell.Offset(-2, 0) = "875" Or cell.Offset(-2, 0) = "850") And cell.Offset(-1, 0) = cell.Offset(-1, -1) Then
cell.Offset(5, 0) = TimeAvg
End If
cell.Offset(5, 0) = TimeAvg
End If
cell.Offset(5, 0) = TimeAvg
End If
cell.Offset(5, 0) = TimeAvg
End If
Next cell
End Sub
I would actually prefer to have done this as a worksheet function, but I wasn't sure how to go about making the arguments as ranges among other things. I've also calculated the column I to show the results im hoping for. I'll use this with a much larger data set later on.
Please let me know if there is any more information that I can or should provide. Any help is greatly appreciated.
Thanks,
Chris