select case and looping problem

[alienscript]

Dear VBAX forum experts,

first of all, I apologize this is Sunday and giving you some question. but I have working thru office work all day, not having rest and am still stuck with this problem with nested-if and looping. It is more than 10,000 rows in real data and I cant do by filtering manually and also 7 nest-if are barely suffice. so I attach only a small sample and it covers all the wanted columns.

below are part of my incorrect code



ElseIf CountA(Range("BI" & .Row) & ":" & Range("BN" & .Row)) = 0

ElseIf .Offset(0, 4) > 0 And (Average(Range("BL" & .Row) & ":" & Range("BN" & .Row)) < .Offset(0, 4) Or Average(Range("BI" & .Row) & ":" & Range("BN" & .Row)) < .Offset(0, 4))


In Excel formulae, it is:
If AND(H2>0, OR(Average(BL2:BN2)<H2, Average(BI2:BN2)<H2)), then.., else...)

I have named $D2:$D100 as Range("Material") and my wanted output is in column AI.

I hope that someone will kindly help me to amend part of my incorrect syntax and also how to loop from D2 until the last row in the named Range and put the answers in column AI. Thank you very very much in advance.

[daniel_d_n_r]

shouldn't there be a 'then' in there, or am I missing the point?

[daniel_d_n_r]

Sorry I have made an error here.
But I am not 100% on the formula as below.


'AND(TRUE, TRUE) equals TRUE'

[alienscript]

Daniel,

I am just not sure how to do it without hardcoding it in VB for the Excel formula Counta and Sum() functions. Really appreciate if you could take another look at it and see if you could help me. Thanks very much...



Dim cell As Range
For Each cell In Range("Material")
With cell
Select Case .Offset(0, -1)
Case Is = "2800"
If .Offset(0, 4).Value = .Offset(0, 32).Value Then
ActiveCell.Value = "no mismatch"
ElseIf .Offset(0, 4) = 0 And .Offset(0, 64) < 3 And CountA(Range("BI" & .Row) & _
":" & Range("BN" & .Row)) = 0 And .Offset(0, 215) = "" Then
ActiveCell.Value = "ok to remove safety-stock"

ElseIf .Offset(0, 4) > 0 And (Average(Range("BL" & .Row) & ":" & Range("BN" & .Row)) _
< .Offset(0, 4) Or Average(Range("BI" & .Row) & ":" & Range("BN" & .Row)) _
< .Offset(0, 4)) Then
ActiveCell.Value = "ok to remove safety-stock"

Else
ActiveCell.Value = "please check further"

Case Is = "2880"
If .Offset(0, 4) = 0 And .Offset(0, 78) < 3 And CountA(Range("BW" & .Row) & ":" _
& Range("CB" & .Row)) = 0 And .Offset(0, 220) = "" Then
ActiveCell.Value = "ok to remove safety-stock"
End Select
End With
Next

[Norie]

Can you explain in words what you want to do?

Do you even need code for this?

[alienscript]

Dear Norie,

I'm sorry for the trouble on Sunday night. I am stuck whole Sunday and looks like I have to temporarily live with 5 columns of formula before I have the answer from VBA. :( Below is the Excel formula of what I want to achieve but I find it very tough to explain in brief. I hope you dont mind I state it out in formula.


For Range("C".Row).value="2800"
=IF(AND(C2="2800",H2=AJ2),"no mismatch",IF(AND(C2="2800",H2=0,AJ2>0,CD2<3,COUNTA(BW2:CB2)=0,COUNTA(EA2:EF2)=0,HK2=""),"ok to remove safety-stock",IF(AND(C2="2800",H2>0,AJ2>H2,OR(AVERAGE(BZ2:CB2,1)<H2,AVERAGE(BW2:CB2,1)<H2)),"ok to reduce safety-stock",IF(AND(C2="2800",H2>AJ2,SUM(AU2:AZ2)>0),"ok to increase safety-stock",IF(C2="2800","need further check","")))))

For Range("C".Row).value="2880"
=IF(AND(C2="2880",H2=AK2),"no mismatch",IF(AND(C2="2880",H2=0,AK2>0,CD2<3,COUNTA(BW2:CB2)=0,HP2=""),"ok to remove safety-stock",IF(AND(C2="2880",H2>0,AK2>H2,OR(AVERAGE(BZ2:CB2,1)<H2,AVERAGE(BW2:CB2,1)<H2)),"ok to reduce safety-stock",IF(AND(C2="2880",H2>AK2,SUM(AU2:AZ2)>0),"ok to increase safety-stock",IF(C2="2880","need further check","")))))

For Range("C".Row).value="2870"
=IF(AND(C2="2870",H2=AL2),"no mismatch",IF(AND(C2="2870",H2=0,AL2>0,DF2<3,COUNTA(CK2:CP2)=0,HK2=""),"ok to remove safety-stock",IF(AND(C2="2870",H2>0,AL2>H2,OR(AVERAGE(CN2:CP2,1)<H2,AVERAGE(CK2:CP2,1)<H2)),"ok to reduce safety-stock",IF(AND(C2="2870",H2>AL2,SUM(AU2:AZ2)>0),"ok to increase safety-stock",IF(C2="2870","need further check","")))))

For Range("C".Row).value="2500"
=IF(AND(C2="2500",H2=AM2),"no mismatch",IF(AND(C2="2500",H2=0,AM2>0,DF2<3,COUNTA(CY2..DD2)=0,COUNTA(EO2:ET2)=0,HO2=""),"ok to remove safety-stock",IF(AND(C2="2500",H2>0,AM2>H2,OR(AVERAGE(DB2..DD2,1)<H2,AVERAGE(CY2..DD2,1)<H2)),"ok to reduce safety-stock",IF(AND(C2="2500",H2>AM2,SUM(AU2:AZ2)>0),"ok to increase safety-stock",IF(C2="2500","need further check","")))))

For Range("C".Row).value="2510"
=IF(AND(C2="2510",H2=AN2),"no mismatch",IF(AND(C2="2510",H2=0,AN2>0,DT2<3,COUNTA(DM2..DR2)=0,COUNTA(FC2:FH2)=0,HN2=""),"ok to remove safety-stock",IF(AND(C2="2510",H2>0,AN2>H2,OR(AVERAGE(DP2..DR2,1)<H2,AVERAGE(DM2..DR2,1)<H2)),"ok to reduce safety-stock",IF(AND(C2="2510",H2>AN2,SUM(AU2:AZ2)>0),"ok to increase safety-stock",IF(C2="2880","need further check","")))))