vba with multiple if/or criteria

[Godders199]

Hello, I have been using the following code to calculate annual targets,

'Rolling Target'
Dim Addvalues
If Cells(i, 6).Value >= 1 Then
Addvalues = Range("p" & i).Value + Range("s" & i).Value + Range("v" & i).Value + Range("y" & i).Value
Range("ab" & i).Value = Addvalues + IIf(Cells(i, 6) <= 8, Range("f" & i).Value, Sheets("Homepage").Range("o4").Value)
Else
Cells(i, 28).Value = 0
End If

The value of o4 is 8

However i need to add some extra critiera based on the value in column Z

if z <= 3 then it needs to use the value 2 instead of O4
if z 4-6 then it needs to use the value 4 instead of O4
if z 7-9 then it needs to use th value 6 instead of o4

Does anyone know how to alter the code to reflect this.?

 

[Fluff]

Re: vba with muiltple if/or criteria

How about

Dim Addvalues
Dim vadd As Long
If Cells(i, 6).Value >= 1 Then
   Addvalues = Range("p" & i).Value + Range("s" & i).Value + Range("v" & i).Value + Range("y" & i).Value
   Select Case Cells(i, 26)
      Case Is <= 3
         vadd = 2
      Case 4 To 6
         vadd = 4
      Case 7 To 9
         vadd = 6
      Case Else
         vadd = Sheets("Homepage").Range("o4").Value
   End Select
   Range("ab" & i).Value = Addvalues + IIf(Cells(i, 6) <= 8, Range("f" & i).Value, vadd)
Else
   Cells(i, 28).Value = 0
End If

 

[Godders199]

Re: vba with muiltple if/or criteria

thanks for your help

 

[Fluff]

Re: vba with muiltple if/or criteria

You're welcome

 

[Godders199]

Re: vba with muiltple if/or criteria

Hi Fluff
I have amended the code as below, however I think I need tochange the following line
Range("ab" & i).Value = Addvalues +IIf(Cells(i, 6) <= 8, Range("f" & i).Value, vadd)
As the result needs to be the lower of vadd or column 6 ifcolumn 6 less than vadd. I have changed it to.
Range("ab" & i).Value = Addvalues +IIf(Cells(i, 6) <= vadd, Range("f" & i).Value, vadd)
But that does not work.
For example
If column 26 is 6 and column 6 is 2. The result in column abshould be Addvalues +2 ( as 2 is less than Vadd)
If column 26 is 6 and column 6 is 12. The result in columnab should be addvalues + 4 (as vadd is less than column 6)
Hope this makes sense.
Dim Addvalues
Dim vadd As Long
If Cells(i, 6).Value >= 1 Then
Addvalues = Range("p" & i).Value +Range("s" & i).Value + Range("v" & i).Value +Range("y" & i).Value
Select Case Cells(i, 26)
Case Is <= 3
vadd = 2
Case 4 To 6
vadd = 4
Case 7 To 9
vadd = 6
Case Else
vadd =Sheets("Homepage").Range("o4").Value
End Select
Range("ab" & i).Value = Addvalues +IIf(Cells(i, 6) <= 8, Range("f" & i).Value, vadd)
Else
Cells(i, 28).Value = 0
End If

 

[Fluff]

Re: vba with muiltple if/or criteria

In what way doesn't it work?

 

[Godders199]

Re: vba with muiltple if/or criteria

Hi Fluff, it appears if this part of the formula is true " Range("ab" & i).Value = Addvalues + IIf(Cells(i, 6) <= 8" it just inserts the value contained in column 6... it does not appear to consider "Range("f" & i).Value, vadd"
"

 

[Godders199]

Re: vba with muiltple if/or criteria

Sorry here is an example
submissions is column 6
Active months column 26
Rolling target is AB
so in these two examples ab shold be 4, as active months is between 4 an 6

[TABLE="width: 949"]
<colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;" span="2"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;" span="2"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <col width="31" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1133;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;" span="2"> <col width="64" style="width: 48pt;"> <col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 87, bgcolor: #92D050"]Submissions[/TD]
[TD="width: 87, bgcolor: #92D050"]Total Checks[/TD]
[TD="width: 56, bgcolor: #92D050"]File checks[/TD]
[TD="width: 52, bgcolor: #92D050"]CCB checks[/TD]
[TD="width: 67, bgcolor: #92D050"]Call Listening[/TD]
[TD="width: 88, bgcolor: #92D050"]File and CCB[/TD]
[TD="width: 63, bgcolor: #92D050"]File and CL[/TD]
[TD="width: 63, bgcolor: #92D050"]Testing Checks[/TD]
[TD="width: 39, bgcolor: #92D050"]Q1 IIP[/TD]
[TD="width: 40, bgcolor: #92D050"]Q1 WCO[/TD]
[TD="width: 40, bgcolor: #92D050"]extra check[/TD]
[TD="width: 36, bgcolor: #92D050"]Q2 IIP[/TD]
[TD="width: 39, bgcolor: #92D050"]Q2 WCO[/TD]
[TD="width: 46, bgcolor: #92D050"]extra check[/TD]
[TD="width: 31, bgcolor: #92D050"]Q3 IIP[/TD]
[TD="width: 36, bgcolor: #92D050"]Q3 WCO[/TD]
[TD="width: 51, bgcolor: #92D050"]extra check[/TD]
[TD="width: 35, bgcolor: #92D050"]Q4 IIP[/TD]
[TD="width: 46, bgcolor: #92D050"]Q4 WCO[/TD]
[TD="width: 46, bgcolor: #92D050"]extra check[/TD]
[TD="width: 64, bgcolor: #92D050"]Months Active[/TD]
[TD="width: 91, bgcolor: #92D050"]Total GS Checks[/TD]
[TD="width: 64, bgcolor: #92D050"]Rolling Target[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Re: vba with muiltple if/or criteria

It works for me, I got 4 for both rows & when I changed the 9 to 3 in submissions, I got 3 for rolling target.
Check that your numbers are actual numbers, rather than text.

 

[Godders199]

Re: vba with muiltple if/or criteria

Thanks again, i will play with the formats, the information is transposed onto the sheet so maybe i need to insert a save before the calculations