PDA

View Full Version : Help is multiple nested IF statements using range objects



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

SamT
03-15-2017, 10:58 AM
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
03-15-2017, 04:15 PM
SamT,

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

Thanks,

Chris

cwb1021
03-16-2017, 04:04 PM
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
03-16-2017, 04:57 PM
YW