none
Write and drop down formula RRS feed

  • Question

  • Hello,

    Today I tried to write a macro which would basically write several functions, say in column B, C and D, and drop down automatically until it hits the last row of column A containing data.

    For that, I wrote a very basic macro which, at first, worked perfectly. Here is an example:

    Sheets("AERoles").Select
        Range("C2").Formula = "=IFERROR(VLOOKUP(A2,Roles!J:J,1,0),""NOK"")"
        Range("C2", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("D2").Formula = "=IF(OR(A2="",B2="")=TRUE,""NOK"",""OK"")"
        Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("E2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",A2))=TRUE,ISNUMBER(SEARCH("";"",B2))=TRUE),""NOK"")"
        Range("E2", "E" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

    On this sheet I have data in columns A & B and the functions must be inserted in columns C, D & E.

    All in all, I have 8 tabs for which I want to do the same action. In the code displayed below, you'll find the 8 tabs I am talking about. To build this macro, I followed the exact same steps to write each line. Then I pressed the button and I realized it worked perfectly...except for 2 tabs.

    In the tabs "ACCOUNTS" and "Entitlements", for some reason, it just copy and paste the headers that I have in row 1 into row 2. It doesn't write any formula. I have been trying to figure out why the whole afternoon as the code follows the exact same method for these two tabs but I don't find any solution.

    Here is my complete code:

    Sub FillFormula()
    Sheets("Roles").Select
        Range("N2").Formula = "=IFERROR(VLOOKUP(RC[-9],helpers!C[-11],1,0),""NOK"")"
        Range("N2", "N" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("O2").Formula = "=IFERROR(VLOOKUP(G2,helpers!C:C,1,0),""NOK"")"
        Range("O2", "O" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("P2").Formula = "=IFERROR(VLOOKUP(H2,helpers!C:C,1,0),""NOK"")"
        Range("P2", "P" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("Q2").Formula = "=IFERROR(VLOOKUP(F2,helpers!D:D,1,0),""NOK"")"
        Range("Q2", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("R2").Formula = "=IF(OR(A2="",B2="",C2="",D2="",E2="",F2="",G2="",H2="")=TRUE,""NOK"",""OK"")"
        Range("R2", "R" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("S2").Formula = "=IF(IF(RC[-14]=""True"",RC[-9]<>"""",""OK"")=FALSE,""NOK"",""OK"")"
        Range("S2", "S" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("T2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",A2))=TRUE,ISNUMBER(SEARCH("";"",C2))=TRUE,ISNUMBER(SEARCH("";"",D2))=TRUE,ISNUMBER(SEARCH("";"",E2))=TRUE,ISNUMBER(SEARCH("";"",F2))=TRUE,ISNUMBER(SEARCH("";"",H2))=TRUE,ISNUMBER(SEARCH("";"",J2))=TRUE,ISNUMBER(SEARCH("";"",B2))=TRUE,ISNUMBER(SEARCH("";"",G2))=TRUE),""NOK"")"
        Range("T2", "T" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("U2").Formula = "=IF(COUNTIF(B:B,B2)>1,""NOK"",""OK"")"
        Range("U2", "U" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("V2").Formula = "=IF(COUNTIF(A:A,A2)>1,""NOK"",""OK"")"
        Range("V2", "V" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("W2").Formula = "=IF(LEN(RC[-21])-LEN(SUBSTITUTE(RC[-21],"" "",""""))>0,""NOK"",""OK"")"
        Range("W2", "W" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    
    Sheets("AERoles").Select
        Range("C2").Formula = "=IFERROR(VLOOKUP(A2,Roles!J:J,1,0),""NOK"")"
        Range("C2", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("D2").Formula = "=IF(OR(A2="",B2="")=TRUE,""NOK"",""OK"")"
        Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("E2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",A2))=TRUE,ISNUMBER(SEARCH("";"",B2))=TRUE),""NOK"")"
        Range("E2", "E" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        
    Sheets("Role_Entitlement_Mapping").Select
        Range("D2").Formula = "=IFERROR(VLOOKUP(A2,Roles!B:B,1,0),""NOK"")"
        Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("E2").Formula = "=IFERROR(VLOOKUP(B2,Entitlements!B:B,1,0),""NOK"")"
        Range("E2", "E" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("F2").Formula = "=IF(OR(A2="""",B2="""",C2="""")=TRUE,""NOK"",""OK"")"
        Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("G2").Formula = "=IF(LEN(RC[-4])-LEN(SUBSTITUTE(RC[-4],"" "",""""))>0,""NOK"",""OK"")"
        Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("H2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",A2))=TRUE,ISNUMBER(SEARCH("";"",B2))=TRUE,ISNUMBER(SEARCH("";"",C2))=TRUE),""NOK"")"
        Range("H2", "H" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("I2").Formula = "=IF(C2=Role_Importer!$E$2,""OK"",""NOK"")"
        Range("I2", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        
    Sheets("Person_ESet_Mapping").Select
        Range("E2").Formula = "=IFERROR(IFERROR(VLOOKUP(RC[-4],ACCOUNTS!C[-1],1,0),VLOOKUP(RC[-4],ACCOUNTS!C[24],1,0)),""NOK"")"
        Range("E2", "E" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("F2").Formula = "=IFERROR(VLOOKUP(RC[-4],Roles!C[-4],1,0),""NOK"")"
        Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("G2").Formula = "=IF(OR(RC[-5]="""",RC[-4]="""",RC[-6]="""")=TRUE,""NOK"",""OK"")"
        Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("H2").Formula = "=IF(LEN(RC[-5])-LEN(SUBSTITUTE(RC[-5],"" "",""""))>0,""NOK"",""OK"")"
        Range("H2", "H" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("I2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",RC[-8]))=TRUE,ISNUMBER(SEARCH("";"",RC[-7]))=TRUE,ISNUMBER(SEARCH("";"",RC[-6]))=TRUE),""NOK"")"
        Range("I2", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("J2").Formula = "=IF(COUNTIFS(C[-9],RC[-9],C[-8],RC[-8])>1,""NOK"",""OK"")"
        Range("J2", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("K2").Formula = "=IF(RC[-8]=Role_Importer!R2C5,""OK"",""NOK"")"
        Range("K2", "K" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    
    Sheets("ACCOUNTS").Select
        Range("O2").Formula = "=IFERROR(VLOOKUP(G2,helpers!A:A,1,0),""NOK"")"
        Range("O2", "O" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("P2").Formula = "=IF(COUNTIF(D:D,D2)=1,G2=""Primary"",""Check Accounttype"")"
        Range("P2", "P" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("Q2").Formula = "=IFERROR(VLOOKUP(D2,Person_ESet_Mapping!A:A,1,0),""NOK"")"
        Range("Q2", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("R2").Formula = "=IFERROR(VLOOKUP(C2,ACCOUNT_ENTITLEMENT_Mapping!A:A,1,0),""NOK"")"
        Range("R2", "R" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("S2").Formula = "=IFERROR(VLOOKUP(I2,helpers!C:C,1,0),""NOK"")"
        Range("S2", "S" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("T2").Formula = "=IFERROR(VLOOKUP(M2,helpers!C:C,1,0),""NOK"")"
        Range("T2", "T" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("U2").Formula = "=IF(OR(C2="""",D2="""",G2="""",I2="""",J2="""",K2="""",M2="""")=TRUE,""NOK"",""OK"")"
        Range("U2", "U" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("V2").Formula = "=IF(I2=""FALSE"",""NOK"",""OK"")"
        Range("V2", "V" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("W2").Formula = "=IF(LEN(J2)-LEN(SUBSTITUTE(J2,"" "",""""))>0,""NOK"",""OK"")"
        Range("W2", "W" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("X2").Formula = "=IFERROR(VLOOKUP(D2,GSD!C:C,1,0),""NOK"")"
        Range("X2", "X" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("Y2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",A2))=TRUE,ISNUMBER(SEARCH("";"",C2))=TRUE,ISNUMBER(SEARCH("";"",D2))=TRUE,ISNUMBER(SEARCH("";"",E2))=TRUE,ISNUMBER(SEARCH("";"",F2))=TRUE,ISNUMBER(SEARCH("";"",H2))=TRUE,ISNUMBER(SEARCH("";"",J2))=TRUE,ISNUMBER(SEARCH("";"",B2))=TRUE,ISNUMBER(SEARCH("";"",G2))=TRUE,ISNUMBER(SEARCH(""-"",A2))=TRUE,ISNUMBER(SEARCH(""-"",C2))=TRUE,ISNUMBER(SEARCH(""-"",D2))=TRUE,ISNUMBER(SEARCH(""-"",E2))=TRUE,ISNUMBER(SEARCH(""-"",F2))=TRUE,ISNUMBER(SEARCH(""-"",H2))=TRUE,ISNUMBER(SEARCH(""-"",J2))=TRUE,ISNUMBER(SEARCH(""-"",B2))=TRUE,ISNUMBER(SEARCH(""-"",G2))=TRUE),""NOK"")"
        Range("Y2", "Y" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("Z2").Formula = "=IF(IF(OR(G2=""primary"",G2=""shared"")=FALSE,AC2=Role_Entitlement_Mapping!$C$2&""_""&ACCOUNTS!C2,""OK"")=FALSE,""NOK"",""OK"")"
        Range("Z2", "Z" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("AA2").Formula = "=IF(OR(G2=""primary"",G2=""shared"")=FALSE,IFERROR(VLOOKUP(N2,Roles!B:B,1,0),""NOK""))"
        Range("AA2", "AA" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("AB2").Formula = "=IF(OR(G2=""primary"",G2=""shared"",N2=AC2)=TRUE,""OK"",IFERROR(SEARCH(G2,N2),""NOK""))"
        Range("AB2", "AB" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("AD2").Formula = "=IF(J2=Role_Importer!$E$2,""OK"",""NOK"")"
        Range("AD2", "AD" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("AE2").Formula = "=IF(EXACT(UPPER(LEFT(G2,1)),LEFT(G2,1))=FALSE,""NOK"",""OK"")"
        Range("AE2", "AE" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    
    Sheets("ACCOUNT_ENTITLEMENT_Mapping").Select
        Range("E2").Formula = "=IFERROR(VLOOKUP(RC[-4],ACCOUNTS!C[-2],1,0),""NOK"")"
        Range("E2", "E" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("F2").Formula = "=IFERROR(VLOOKUP(RC[-4],Entitlements!C[-4],1,0),""NOK"")"
        Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("G2").Formula = "=IFERROR(VLOOKUP(RC[-3],helpers!C[-5],1,0),""NOK"")"
        Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("H2").Formula = "=IF(OR(RC[-7]="""",RC[-5]="""",RC[-4]="""",RC[-6]="""")=TRUE,""NOK"",""OK"")"
        Range("H2", "H" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("I2").Formula = "=IF(COUNTIFS(C[-8],RC[-8],C[-7],RC[-7])>1,""NOK"",""OK"")"
        Range("I2", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("J2").Formula = "=IF(LEN(RC[-7])-LEN(SUBSTITUTE(RC[-7],"" "",""""))>0,""NOK"",""OK"")"
        Range("J2", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("K2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",RC[-10]))=TRUE,ISNUMBER(SEARCH("";"",RC[-9]))=TRUE,ISNUMBER(SEARCH("";"",RC[-8]))=TRUE,ISNUMBER(SEARCH("";"",RC[-7]))=TRUE),""NOK"")"
        Range("K2", "K" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("L2").Formula = "=IF(RC[-9]=Role_Importer!R2C5,""OK"",""NOK"")"
        Range("L2", "L" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    
    Sheets("Entitlements").Select
        Range("H2").Formula = "=IF(COUNTIF(C[-6],RC[-6])>1,""NOK"",""OK"")"
        Range("H2", "H" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("I2").Formula = "=IFERROR(VLOOKUP(RC[-3],helpers!C[-7],1,0),""NOK"")"
        Range("I2", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("J2").Formula = "=IF(OR(RC[-8]="""",RC[-5]="""",RC[-4]="""")=TRUE,""NOK"",""OK"")"
        Range("J2", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("K2").Formula = "=IF(LEN(RC[-6])-LEN(SUBSTITUTE(RC[-6],"" "",""""))>0,""NOK"",""OK"")"
        Range("K2", "K" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("L2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",RC[-10]))=TRUE,ISNUMBER(SEARCH("";"",RC[-9]))=TRUE,ISNUMBER(SEARCH("";"",RC[-7]))=TRUE,ISNUMBER(SEARCH("";"",RC[-6]))=TRUE),""NOK"")"
        Range("L2", "L" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("M2").Formula = "=IF(RC[-8]=Role_Importer!R2C5,""OK"",""NOK"")"
        Range("M2", "M" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    
    Sheets("IDM Admins").Select
        Range("D2").Formula = "=IF(OR(RC[-3]="""",RC[-2]="""",RC[-1]="""")=TRUE,""NOK"",""OK"")"
        Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("E2").Formula = "=IF(LEN(RC[-2])-LEN(SUBSTITUTE(RC[-2],"" "",""""))>0,""NOK"",""OK"")"
        Range("E2", "E" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("F2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",RC[-5]))=TRUE,ISNUMBER(SEARCH("";"",RC[-4]))=TRUE,ISNUMBER(SEARCH("";"",RC[-3]))=TRUE),""NOK"")"
        Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Range("G2").Formula = "=IF(RC[-4]=Role_Importer!R2C5,""OK"",""NOK"")"
        Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    
    
    End Sub

    I hope that someone here could help me figure this out. This has been bothering me a lot today. Thanks.

    Best

    Tuesday, January 7, 2020 4:48 PM

Answers

  • Perhaps column A is empty (blank) in those two sheets?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by charliecoch1 Monday, January 13, 2020 9:58 AM
    Tuesday, January 7, 2020 8:45 PM

All replies

  • Perhaps column A is empty (blank) in those two sheets?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by charliecoch1 Monday, January 13, 2020 9:58 AM
    Tuesday, January 7, 2020 8:45 PM
  • Indeed it is!

    I am trying to tell the computer it has to look at column C so I modified the code as you can see below but so far it doesn't work.

    Sheets("ACCOUNTS").Select
        Dim ACCOUNTS As Worksheet
        Set ACCOUNTS = Worksheets("ACCOUNTS")
        Dim ACCOUNTSCell As Range
        Dim m As Long
        m = ACCOUNTS.Range("C" & ACCOUNTS.Rows.Count).End(xlUp).Row
        For Each ACCOUNTSCell In ACCOUNTS.Range("C2:C" & m)
            Range("O2").Formula = "=IFERROR(VLOOKUP(G2,helpers!A:A,1,0),""NOK"")"
            Range("O2", "O" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("P2").Formula = "=IF(COUNTIF(D:D,D2)=1,G2=""Primary"",""Check Accounttype"")"
            Range("P2", "P" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("Q2").Formula = "=IFERROR(VLOOKUP(D2,Person_ESet_Mapping!A:A,1,0),""NOK"")"
            Range("Q2", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("R2").Formula = "=IFERROR(VLOOKUP(C2,ACCOUNT_ENTITLEMENT_Mapping!A:A,1,0),""NOK"")"
            Range("R2", "R" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("S2").Formula = "=IFERROR(VLOOKUP(I2,helpers!C:C,1,0),""NOK"")"
            Range("S2", "S" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("T2").Formula = "=IFERROR(VLOOKUP(M2,helpers!C:C,1,0),""NOK"")"
            Range("T2", "T" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("U2").Formula = "=IF(OR(C2="""",D2="""",G2="""",I2="""",J2="""",K2="""",M2="""")=TRUE,""NOK"",""OK"")"
            Range("U2", "U" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("V2").Formula = "=IF(I2=""FALSE"",""NOK"",""OK"")"
            Range("V2", "V" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("W2").Formula = "=IF(LEN(J2)-LEN(SUBSTITUTE(J2,"" "",""""))>0,""NOK"",""OK"")"
            Range("W2", "W" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("X2").Formula = "=IFERROR(VLOOKUP(D2,GSD!C:C,1,0),""NOK"")"
            Range("X2", "X" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("Y2").Formula = "=IF(OR(ISNUMBER(SEARCH("";"",A2))=TRUE,ISNUMBER(SEARCH("";"",C2))=TRUE,ISNUMBER(SEARCH("";"",D2))=TRUE,ISNUMBER(SEARCH("";"",E2))=TRUE,ISNUMBER(SEARCH("";"",F2))=TRUE,ISNUMBER(SEARCH("";"",H2))=TRUE,ISNUMBER(SEARCH("";"",J2))=TRUE,ISNUMBER(SEARCH("";"",B2))=TRUE,ISNUMBER(SEARCH("";"",G2))=TRUE,ISNUMBER(SEARCH(""-"",A2))=TRUE,ISNUMBER(SEARCH(""-"",C2))=TRUE,ISNUMBER(SEARCH(""-"",D2))=TRUE,ISNUMBER(SEARCH(""-"",E2))=TRUE,ISNUMBER(SEARCH(""-"",F2))=TRUE,ISNUMBER(SEARCH(""-"",H2))=TRUE,ISNUMBER(SEARCH(""-"",J2))=TRUE,ISNUMBER(SEARCH(""-"",B2))=TRUE,ISNUMBER(SEARCH(""-"",G2))=TRUE),""NOK"")"
            Range("Y2", "Y" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("Z2").Formula = "=IF(IF(OR(G2=""primary"",G2=""shared"")=FALSE,AC2=Role_Entitlement_Mapping!$C$2&""_""&ACCOUNTS!C2,""OK"")=FALSE,""NOK"",""OK"")"
            Range("Z2", "Z" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("AA2").Formula = "=IF(OR(G2=""primary"",G2=""shared"")=FALSE,IFERROR(VLOOKUP(N2,Roles!B:B,1,0),""NOK""))"
            Range("AA2", "AA" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("AB2").Formula = "=IF(OR(G2=""primary"",G2=""shared"",N2=AC2)=TRUE,""OK"",IFERROR(SEARCH(G2,N2),""NOK""))"
            Range("AB2", "AB" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("AD2").Formula = "=IF(J2=Role_Importer!$E$2,""OK"",""NOK"")"
            Range("AD2", "AD" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
            Range("AE2").Formula = "=IF(EXACT(UPPER(LEFT(G2,1)),LEFT(G2,1))=FALSE,""NOK"",""OK"")"
            Range("AE2", "AE" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
        Next ACCOUNTSCell

    Wednesday, January 8, 2020 8:37 AM
  • Ok after struggling so much with that, I realized I only had to change Cell(Rows.Count, 1) into Cell(Rows.Count, X)

    With X the number of the column starting from A. Now it works fine. Thank you :)

    Wednesday, January 8, 2020 9:27 AM