Help is multiple nested IF statements using range objects

[cwb1021]

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
image 1.jpg

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

[SamT]

I know you made one mistake (C12 <> C11) so I'm going to post this, even though it doesn't agree with your results

Enum ColumnAndArrayIndices
  Names = 1
  Sizes
  Forms
  Sects
  Mins
  Maxs
  Diffs
  Times
  TimeAvgs
End Enum


Sub CWB1021()
Dim Data_Set As Variant
Dim i As Long

Dim V1225DiffSum As Double
Dim V1225TimeSum As Double
Dim V1225Ave As Double

Dim V875DiffSum As Double
Dim V875TimeSum As Double
Dim V875Ave As Double

Dim C875DiffSum As Double
Dim C875TimeSum As Double
Dim C875Ave As Double

Dim L850DiffSum As Double
Dim L850TimeSum As Double
Dim L850Ave As Double

Dim L875DiffSum As Double
Dim L875TimeSum As Double
Dim L875Ave As Double



Data_Set = Sheets("Sheet1").UsedRange

For i = 3 To UBound(Data_Set)
  If Data_Set(i, Sects) = "V" Then
    If Data_Set(i, Sizes) = 1225 Then
      V1225DiffSum = V1225DiffSum + Data_Set(i, Diffs)
      V1225TimeSum = V1225TimeSum + Data_Set(i, Times)
    ElseIf Data_Set(i, Sizes) = 875 Then
      V875DiffSum = V875DiffSum + Data_Set(i, Diffs)
      V875TimeSum = V875TimeSum + Data_Set(i, Times)
    End If
    
  ElseIf Data_Set(i, Sects) = "C" Then
    If Data_Set(i, Sizes) = 875 _
    And Data_Set(i, Forms) = Data_Set(i - 1, Forms) Then
      C875DiffSum = C875DiffSum + Data_Set(i, Diffs)
      C875TimeSum = C875TimeSum + Data_Set(i, Times)
    End If
    
   ElseIf Data_Set(i, Sects) = "L" _
    And Data_Set(i, Forms) = Data_Set(i - 1, Forms) Then
      If Data_Set(i, Sizes) = 850 Then
        L850DiffSum = L850DiffSum + Data_Set(i, Diffs)
        L850TimeSum = L850TimeSum + Data_Set(i, Times)
      ElseIf Data_Set(i, Sizes) = 875 Then
        L875DiffSum = L875DiffSum + Data_Set(i, Diffs)
        L875TimeSum = L875TimeSum + Data_Set(i, Times)
      End If
  End If
Next


V1225Ave = V1225DiffSum / V1225TimeSum
V875Ave = V875DiffSum / V875TimeSum
C875Ave = C875DiffSum / C875TimeSum
L850Ave = L850DiffSum / L850TimeSum
L875Ave = L875DiffSum / L875TimeSum


With Sheets("Sheet1").Columns(TimeAvgs)
  For i = 3 To UBound(Data_Set)
    If Data_Set(i, Sects) = "V" Then
      If Data_Set(i, Sizes) = 1225 Then
        .Cells(i) = V1225Ave
      ElseIf Data_Set(i, Sizes) = 875 Then
        .Cells(i) = V875Ave
      End If
      
    ElseIf Data_Set(i, Sects) = "C" Then
      If Data_Set(i, Sizes) = 875 _
      And Data_Set(i, Forms) = Data_Set(i - 1, Forms) Then
        .Cells(i) = C875Ave
      End If
      
   ElseIf Data_Set(i, Sects) = "L" _
    And Data_Set(i, Forms) = Data_Set(i - 1, Forms) Then
      If Data_Set(i, Sizes) = 850 Then
        .Cells(i) = L850Ave
      ElseIf Data_Set(i, Sizes) = 875 Then
        .Cells(i) = L875Ave
      End If
    End If
  Next
End With

End Sub

[cwb1021]

SamT,

Thanks for your reply! I'll try this out and post the results.

Thanks,

Chris

[cwb1021]

SamT,

The code works well for the most part. There are a couple of things not calculating correctly, but I believe this may be because of an error in the logic I posted. I'll try to tweak it based on the code you provided and will post back here if I run in to a wall.

Thanks again for the help! Its greatly appreciated.

Thanks,

Chris

[SamT]

YW