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

Submissions	Total Checks	File checks	CCB checks	Call Listening	File and CCB	File and CL	Testing Checks	Q1 IIP	Q1 WCO	extra check	Q2 IIP	Q2 WCO	extra check	Q3 IIP	Q3 WCO	extra check	Q4 IIP	Q4 WCO	extra check	Months Active	Total GS Checks	Rolling Target
11	7	6	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	4	7	8
9	5	3	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	4	5	8

<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> </tbody>

 

[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