Need help for if statement

[prakashjoshi87]

here is sheet
[TABLE="class: cms_table, width: 454"]
<tbody>[TR]
[TD="class: cms_table_et2, width: 101"]ORDER QTY[/TD]
[TD="class: cms_table_et2, width: 80"]PROCESS[/TD]
[TD="class: cms_table_et2, width: 129"]Load for Process 1[/TD]
[TD="class: cms_table_et2, width: 72"]Load for Process 2[/TD]
[TD="class: cms_table_et2, width: 72"]Load for Process 3[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 101"]45000[/TD]
[TD="class: cms_table_et2, width: 80"]1+2+3[/TD]
[TD="class: cms_table_et2, width: 129"]45000[/TD]
[TD="class: cms_table_et2, width: 72"]45000[/TD]
[TD="class: cms_table_et2, width: 72"]45000[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 101"]50000[/TD]
[TD="class: cms_table_et2, width: 80"]1+3[/TD]
[TD="class: cms_table_et2, width: 129"]50000[/TD]
[TD="class: cms_table_et2, width: 72"][/TD]
[TD="class: cms_table_et2, width: 72"]50000[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 101"]6000[/TD]
[TD="class: cms_table_et2, width: 80"]1+2[/TD]
[TD="class: cms_table_et2, width: 129"]6000[/TD]
[TD="class: cms_table_et2, width: 72"]6000[/TD]
[TD="class: cms_table_et2, width: 72"][/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 101"]65000[/TD]
[TD="class: cms_table_et2, width: 80"]1+2+3[/TD]
[TD="class: cms_table_et2, width: 129"]65000[/TD]
[TD="class: cms_table_et2, width: 72"]65000[/TD]
[TD="class: cms_table_et2, width: 72"]65000[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 101"]70000[/TD]
[TD="class: cms_table_et2, width: 80"]1+2[/TD]
[TD="class: cms_table_et2, width: 129"]70000[/TD]
[TD="class: cms_table_et2, width: 72"]70000[/TD]
[TD="class: cms_table_et2, width: 72"][/TD]
[/TR]
</tbody>[/TABLE]


This is the sheet
in process column if i choose 1+2+3 then automatic load will show on process 1,process 2 and process
or if i choose 1+3 then load will show on process 1 and process 3
and if is choose 1+2 then load will show on process 1 and 2 only

Need a vb code for do this
Pls help me

 

[YasserKhalil]

In Column Process : Have you got validation list?
What do you mean by load? Load what? From which place do you load your data in Columns(C:E)?

 

[prakashjoshi87]

yes in process column i have validation list
Load mean order qty which i have to produce in process 1 process 2 process 3

i have order qty before process column from there i load my data in columns (C:E)

 

[YasserKhalil]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 1 And Target.Column = 2 Then
        Target.Offset(, 1).Resize(1, 3).ClearContents
        Dim lRow As Long: lRow = Target.Row
        With Target
            If Target = "1+2" Then
                Range(Cells(lRow, 3), Cells(lRow, 4)) = .Offset(, -1)
            ElseIf Target = "1+3" Then
                Cells(lRow, 3) = .Offset(, -1): Cells(lRow, 5) = .Offset(, -1)
            ElseIf Target = "1+2+3" Then
                Range(Cells(lRow, 3), Cells(lRow, 5)) = .Offset(, -1)
            ElseIf Target = "2+3" Then
                Range(Cells(lRow, 4), Cells(lRow, 5)) = .Offset(, -1)
            End If
        End With
    End If
End Sub

 

[proficient]

I really made this very complicated but you will get the desire result you want

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

ORDER QTY​

[/TD]
[TD]

PROCESS​

[/TD]
[TD]

Load for Process 1​

[/TD]
[TD]

Load for Process 2​

[/TD]
[TD]

Load for Process 3​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

45000​

[/TD]
[TD]

2+3​

[/TD]
[TD][/TD]
[TD]

45000​

[/TD]
[TD]

45000​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

50000​

[/TD]
[TD]

1+3​

[/TD]
[TD]

50000​

[/TD]
[TD][/TD]
[TD]

50000​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

6000​

[/TD]
[TD]

1+2​

[/TD]
[TD]

6000​

[/TD]
[TD]

6000​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Put this in C2:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($C$1,1),B2:B2)))*A2:A2)=0,"",SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($C$1,1),B2:B2)))*A2:A2))

This in D2:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($D$1,1),B2:B2)))*A2:A2)=0,"",SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($D$1,1),B2:B2)))*A2:A2))

This in E2:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($E$1,1),B2:B2)))*A2:A2)=0,"",SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($E$1,1),B2:B2)))*A2:A2))

just copy down...

 

[YasserKhalil]

Another code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 1 And Target.Column = 2 Then
        Dim I As Long, A
        Target.Offset(, 1).Resize(1, 3).ClearContents
        A = Split(Target, "+")
        For I = 0 To UBound(A)
            Cells(Target.Row, A(I) + 2) = Target.Offset(, -1)
        Next I
    End If
End Sub

 

[YasserKhalil]

Hi proficient
if formula
you can put this formula in c2

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT(C$1,1),$B2)))*$A2)=0,"",SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT(C$1,1),$B2)))*$A2))

then drag down and right

 

[proficient]

Thanks, also we can use this

=SUMPRODUCT(--(ISNUMBER(SEARCH(RIGHT($D$1,1),B2)))*A2)
TO AVOID ZERO, just select the entire table, go to conditional formatting click on LESS THAN, type 1 and in custom format, select WHITE FONT COLOR

 

[ukmikeb]

Or :-
Data[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]ORDER QTY[/TD]
[TD]PROCESS[/TD]
[TD]Load for Process 1[/TD]
[TD]Load for Process 2[/TD]
[TD]Load for Process 3[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]45000[/TD]
[TD]1+2+3[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]45000[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]50000[/TD]
[TD]1+3[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]6000[/TD]
[TD]1+2[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]65000[/TD]
[TD]1+2+3[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]65000[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]70000[/TD]
[TD]1+2[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
C2	=(ISNUMBER(SEARCH(COLUMNS($C2:C2),$B2)))*$A2

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]



And apply conditional formatting of "=C2=0" with White Text to apply to cells C2 through E6 to blank out the zeroes.

hth

 

[prakashjoshi87]

Thank you very much
its work
thanks a lot