IF THEN regels
Dit is de volledige macro:
Sub StastiekBijwerken()
'
' StastiekBijwerken Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("A1").Select 'ctrl+home
'Formule: Sales District vs Tabblad "ZIP Code"
Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row) = Application.WorksheetFunction.VLookup(Range("r2:R" & Cells(Rows.Count, "R").End(xlUp).Row), Worksheets("ZIP Code").Range("g:h"), 2, False)
'Kolom Q = "ZIP Code" ==> getal maken
Columns("Q:Q").Select
Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Kolom Y = "Calendar/Qtr" ==> getal maken
Columns("Y:Y").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Kolom Z = "Calendar/Yr" ==> getal maken
Columns("Z:Z").Select
Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Kolom O = "Sold-To" ==> getal maken
Columns("O:O").Select
Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Kolom AF = "Quantity" ==> Format naar "Number"
Columns("AF:AF").Select
Selection.NumberFormat = "0"
'Kolom AG = "Invoice Total" ==> Format naar "Number"
Columns("AG:AG").Select
Selection.NumberFormat = "#,##0.00"
'Kolom AH = "List Price" ==> Format naar "Number"
Columns("AH:AH").Select
Selection.NumberFormat = "#,##0.00"
'Kolom AI = "Calculation 1" ==> Format naar "Number"
Columns("AI:AI").Select
Selection.NumberFormat = "#,##0.00"
'Formule: Sales District vs Tabblad "ZIP Code"
Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row) = Application.WorksheetFunction.VLookup(Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row), Worksheets("ZIP Code").Range("A
"), 4, False)
'Formule: Profit Center vs Commission Group
Range("D2
" & Cells(Rows.Count, "D").End(xlUp).Row) = Application.WorksheetFunction.VLookup(Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row), Worksheets("Rep").Range("W:Z"), 2, False)
'Formule: PC1 vs Commission Group
Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row) = Application.WorksheetFunction.VLookup(Range("D2
" & Cells(Rows.Count, "D").End(xlUp).Row), Worksheets("Rep").Range("X:Z"), 2, False)
'Formule: CG1 vs Profit Center
Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row) = Application.WorksheetFunction.VLookup(Range("D2
" & Cells(Rows.Count, "G").End(xlUp).Row), Worksheets("Rep").Range("X:Z"), 3, False)
'Kolom D ="Profit Center" ==> getal maken
Columns("D
").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Kolom G = "Commission Group" ==> getal maken
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Formule: Sold Customer vs Tabblad "Master Slave"
Range("P2
" & Cells(Rows.Count, "P").End(xlUp).Row) = Application.WorksheetFunction.VLookup(Range("O2:O" & Cells(Rows.Count, "O").End(xlUp).Row), Worksheets("Customer Code").Range("a:B"), 2, False)
Range("I2:I").Select
=IF(AND(F2="CDG BE DL North";H2="CDG QSD Branded");"INGRID DAEMS";IF(AND(F2="CDG BE DL North";H2="CDG QSD Custom");"INGRID DAEMS";IF(AND(F2="CDG BE DL North";H2="CDG Other Comm");"INGRID DAEMS";IF(AND(F2="CDG BE DL South";H2="CDG QSD Branded");"EMILY GENGOUX";IF(AND(F2="CDG BE DL South";H2="CDG QSD Custom");"EMILY GENGOUX";IF(AND(F2="CDG BE DL South";H2="CDG Other Comm");"EMILY GENGOUX";IF(AND(F2="CDG BE DL South";H2="CDG BS");"ALAIN NICANOR";IF(AND(F2="CDG BE DL North";H2="CDG BS");"DIEDRIK FRATILA";IF(AND(F2="CDG BE DL North";H2="CDG Diabetes/hemo");"INGRID DAEMS";IF(AND(F2="CDG BE DL South";H2="CDG Diabetes/hemo");"EMILY GENGOUX";IF(AND(F2="CDG BE DL North";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(F2="CDG BE DL North";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(F2="CDG BE DL South";H2="CDG CID - AI");"EMILY GENGOUX";IF(AND(H2="CDG BE DL South";H2="CDG IDD Serology");"EMILY GENGOUX";IF(AND(Q2>1119;Q2<1500);"EMILY GENGOUX";IF(AND(Q2>1499;Q2<2000);"WENDIE VANHEFFEN";IF(AND(Q2>2999;Q2<3500);"WENDIE V
ANHEFFEN";IF(AND(Q2>3999;Q2<6000);"EMILY GENGOUX";IF(AND(Q2>5999;Q2<6600);"WENDIE VANHEFFEN";IF(AND(Q2>6999;Q2<8800);"WENDIE VANHEFFEN";IF(AND(P2="SONIC";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(P2="SONIC";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(P2="CERBA";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(P2="CERBA";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(P2="SIEMENS";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(P2="SIEMENS";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(P2="VLAAMSE UNIVERSITEIT BRUSSEL";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(P2="VLAAMSE UNIVERSITEIT BRUSSEL";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(P2="UZ BRUSSEL";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(P2="UZ BRUSSEL";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(P2="PPD";H2="CDG CID - AI");"WENDIE VANHEFFEN";IF(AND(P2="PPD";H2="CDG IDD Serology");"WENDIE VANHEFFEN";IF(AND(P2="SONIC";H2="CDG BS");"DIEDRIK FRATELI";IF(AND(P2="CERBA";H2="CDG BS");"DIEDRIK FRATELI";IF(AND(P2="SIEMENS";H2="CDG BS");"DIE
DRIK FRATELI";IF(AND(P2="VLAAMSE UNIVERSITEIT BRUSSEL";H2="CDG BS");"DIEDRIK FRATELI";IF(AND(P2="UZ BRUSSEL";H2="CDG BS");"DIEDRIK FRATELI";IF(AND(P2="PPD";H2="CDG BS");"DIEDRIK FRATELI";IF(AND(P2="MILITAIR HOSPITAL";H2="CDG BS");"DIEDRIK FRATELI";IF(AND(P2="SONIC";H2="CDG Diabetes/Hemo");"INGRID DAEMS";IF(AND(P2="SONIC";H2="CDG QSD Branded");"ingrid daems";IF(AND(P2="CERBA";H2="CDG Diabetes/Hemo");"INGRID DAEMS";IF(AND(P2="CERBA";H2="CDG QSD Branded");"INGRID DAEMS";IF(AND(P2="SIEMENS";H2="CDG Diabetes/Hem");"INGRID DAEMS";IF(AND(P2="SIEMENS";H2="CDG QSD Branded");"INGRID DAEMS";IF(AND(P2="VLAAMSE UNIVERSITEIT BRUSSEL";H2="CDG Diabetes/Hemo");"INGRID DAEMS";IF(AND(P2="VLAAMSE UNIVERSITEIT BRUSSEL";H2="CDG QSD Branded");"INGRID DAEMS";IF(AND(P2="UZ BRUSSEL";H2="CDG Diabetes/Hemo");"INGRID DAEMS";IF(AND(P2="UZ BRUSSEL";H2="CDG QSD Branded");"INGRID DAEMS";IF(AND(P2="PPD";H2="CDG Diabetes/Hemo");"INGRID DAEMS";IF(AND(P2="PPD";H2="CDG QSD Branded");"INGRID DAEMS";IF(P2="SYNLAB";"EMILY GENGOUX";IF(P2="MB NEXT";"
EMILY GENGOUX";IF(AND(C2="Luxembourg";H2="CDG IDD Serology");"EMILY GENGOUX";IF(AND(C2="Luxembourg";H2="CDG CID - AI");"EMILY GENGOUX";IF(AND(C2="Luxembourg";H2="CDG QSD Custom");"EMILY GENGOUX";IF(AND(C2="Luxembourg";H2="CDG QSD Branded");"EMILY GENGOUX";IF(AND(C2="Luxembourg";H2="CDG Diabetes/Hemo");"EMILY GENGOUX";FALSE))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Range("A1").Select
End Sub