Macro to check one column and use formula in another column

[acoene]

If column E contains the text 1ST then i want a formula to run in the column next to it(column F). Then i want to do the same with the text CUCA but if this text it needs to use another formula in the column next to if (column F)

Can someone help me with this?

For example



If in column E is the word "1ST" i want a formula and this formula goes from column F to column AG in the same row. The same if the word is "CUCA" but then the formula is something else as you can see in the picture

Thanks!

 

[ER_Neha]

do you want a vba code ?

 

[acoene]

do you want a vba code ?

Yes I need a vba code because I use an userform to run it to mutiple sheets

 

[acoene]

For the moment i use:

Sub Runcode()


    Dim strFormulas(1 To 6) As Variant
  

    Range("F26").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F27").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F28").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F29").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F30").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F31").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"
  
    Range("F57").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F58").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F59").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F60").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F61").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F62").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"
  
    Range("F88").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F89").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F90").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F91").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F92").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F93").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"
  
    Range("F119").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F120").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F121").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F122").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F123").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F124").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"
  
    Range("F150").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F151").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F152").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F153").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F154").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F155").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"
  
    Range("F181").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F182").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F183").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F184").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F185").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F186").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"
  
    Range("F212").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R2C1,R[-24]C:R[-1]C)"
  
    Range("F213").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R5C1,R[-24]C:R[-1]C)"

      
    Range("F214").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R3C1,R[-24]C:R[-1]C)"

  
    Range("F215").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R4C1,R[-24]C:R[-1]C)"

  
    Range("F216").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R7C1,R[-24]C:R[-1]C)"
  
    Range("F217").Select
    ActiveCell.Formula2R1C1 = "=+countbycolor(R6C1,R[-24]C:R[-1]C)"


        strFormulas(1) = "+countbycolor(R2C1,R[-24]C:R[-1]C)"
        strFormulas(2) = "+countbycolor(R5C1,R[-24]C:R[-1]C)"
        strFormulas(3) = "+countbycolor(R3C1,R[-24]C:R[-1]C)"
        strFormulas(4) = "+countbycolor(R4C1,R[-24]C:R[-1]C)"
        strFormulas(5) = "+countbycolor(R7C1,R[-24]C:R[-1]C)"
        strFormulas(6) = "+countbycolor(R6C1,R[-24]C:R[-1]C)"

      
        Range("F26:AG31").FillRight
        Range("F57:AG62").FillRight
        Range("F88:AG93").FillRight
        Range("F119:AG124").FillRight
        Range("F150:AG155").FillRight
        Range("F181:AG186").FillRight
        Range("F212:AG217").FillRight

End sub