Convert ACTUAL cell formulas / functions into VBA code for these 6 Columns

[ChrisOK]

Not sure how to convert these actual cell formulas into VBA. This code should insert headers for the columns in Row 4, then insert the formulas into Row 5, and last, drag forumals down as far as there is data present in Col A. (don't want it to drag to bottom of page and have a bunch of errored out cells where data doesnt exist)

'-----------------------------------------------------------
    'THIS IS NEW CODE TO INSERT X THROUGH AC FORMULAS TO ACTIVATE SUMMARY TAB RESULTS
    'PUTS IN ALL THE COL HEADERS OF "X THROUGH AC"
    
    
    Range("X4").Select
    ActiveCell.FormulaR1C1 = "NIIN"
    
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = "STATUS"
    
    Range("Z4").Select
    ActiveCell.FormulaR1C1 = "Y1 DMDS"
    
    Range("AA4").Select
    ActiveCell.FormulaR1C1 = "Y2 DMDS"
    
    Range("AB4").Select
    ActiveCell.FormulaR1C1 = "TOT"
    
    Range("AC4").Select
    ActiveCell.FormulaR1C1 = "NSN"
  
'-----------------------------------------------------------

'THIS IS NEW CODE TO INSERT X THROUGH AC FORMULAS TO ACTIVATE SUMMARY TAB RESULTS
' INSERTS THE FORMULAS TO X,Y,Z,AA,AB,AC and drags downward WHERE DATA EXISTS IN COL A'

'THIS FORMULA WAS ENTERED INTO X5
    'Range("X5").Select
    'ActiveCell.FormulaR1C1 = "=MID(RC[-23],5,9)"
    
'THIS FORMULA WAS ENTERED INTO Y5
    'Range("Y5").Select
    'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C[3]:C[4],2,FALSE)"
    
'THIS FORMULA WAS ENTERED INTO Z5
    'Range("Z5").Select
    'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],C[2]:C[4],3,FALSE)"
    
' DRAG FORMULAS FROM X TO Z DOWN TO BOTTOM <<<<<<<<<<<<
    
    'Range("X5:AC5").Select
    'Range("X5:AC5").AutoFill Destination:=Range("X5:AC" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault


    Range("X4").Select
    ActiveCell.FormulaR1C1 = "?????"
    '>>>>THE ABOVE ACTUAL CELL FORMULA IS:  =MID(A5,5,9)
    
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = "??????"
    '>>>>THE ABOVE ACTUAL CELL FORMULA IS:   =COUNTIF(A:A, A5)>1
    
    Range("Z4").Select
    ActiveCell.FormulaR1C1 = "??????"
    '>>>>THE ABOVE ACTUAL CELL FORMULA IS:  =G5
    
    Range("AA4").Select
    ActiveCell.FormulaR1C1 = "??????"
    '>>>>THE ABOVE ACTUAL CELL FORMULA IS:  =I5
    
    Range("AB4").Select
    ActiveCell.FormulaR1C1 = "??????"
    '>>>>THE ABOVE ACTUAL CELL FORMULA IS:  =SUM(Z5+AA5)/730
    
    Range("AC4").Select
    ActiveCell.FormulaR1C1 = "??????"
    '>>>>THE ABOVE ACTUAL CELL FORMULA IS:  =A5)

 

[farmerscott]

Chris,

I have tried to clean up your code but you start to write over the cells X4, Y4 and Z4 towards the end of your code. What do you want to do?

FarmerScott

'-----------------------------------------------------------
'THIS IS NEW CODE TO INSERT X THROUGH AC FORMULAS TO ACTIVATE SUMMARY TAB RESULTS
'PUTS IN ALL THE COL HEADERS OF "X THROUGH AC"
Sub AAA()
Dim lr As Long

' finding the last used row in Col A.
lr = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

' get rid of all your 'select' and 'activecell', it is not nescessary.
Range("X4").Value = "NIIN"

Range("Y4").Value = "STATUS"

Range("Z4").Value = "Y1 DMDS"

Range("AA4").Value = "Y2 DMDS"

Range("AB4").Value = "TOT"

Range("AC4").Value = "NSN"


Range("X5:X" & lr).FormulaR1C1 = "=MID(RC[-23],5,9)"

Range("Y5:Y" & lr).FormulaR1C1 = "=VLOOKUP(RC[-1],C[3]:C[4],2,FALSE)"

Range("Z5:Z" & lr).FormulaR1C1 = "=VLOOKUP(RC[-2],C[2]:C[4],3,FALSE)"

' you are going to copy over the values you put into X4, Y4 and Z4 from above... what do you want to do?
Range("X4").Select
ActiveCell.FormulaR1C1 = "?????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =MID(A5,5,9)

Range("Y4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =COUNTIF(A:A, A5)>1

Range("Z4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =G5

Range("AA4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =I5

Range("AB4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =SUM(Z5+AA5)/730

Range("AC4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =A5)

End Sub

 

[ChrisOK]

Sorry, I saw that after I posted but it was past the edit timeframe -- the copy over was corrected but
Ultimately, I need to know how to convert these formulas/functions.
For example: Y4 should have this formula =COUNTIF(A:A, A5)>1 pasted into the cell using VBA
Here's where it would go: ActiveCell.FormulaR1C1 = "?????"
but it is altering it to: =COUNTIF(AA), 'A5')>1 with extra quote marks and parens around the "(A)" that I belive is causing the formula to error out...

Here's what I've tried using, but sadly, Y4, Z4, AA4, AB4, AC4 do not work... the vba did something to it to make it look differently, and I've got the "#NAME?" error in each of those cells/columns.

PLEASE SEE THE NOTES OF HOW EACH RESULT TURNED OUT:

Range("X5").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-23],5,9)"
    'THE ABOVE ACTUAL CELL FORMULA IS:  =MID(A5,5,9)  ****THIS ONE WORKS!!***** 
    
    Range("Y5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(A:A, A5)>1"
    'THE ABOVE ACTUAL CELL FORMULA IS:   =COUNTIF(A:A, A5)>1  ****HOWEVER, THE RESULT IN THE CELL IS:  =COUNTIF(A:(A), 'A5')>1  (NOTICE HOW IT DIFFERS)
    
    Range("Z5").Select
    ActiveCell.FormulaR1C1 = "=G5"
    'THE ABOVE ACTUAL CELL FORMULA IS:  =G5 ****HOWEVER, THE RESULT IN THE CELL IS:  ='G5'  (NOTICE HOW IT DIFFERS)

    Range("AA5").Select
    ActiveCell.FormulaR1C1 = "=I5"
    'THE ABOVE ACTUAL CELL FORMULA IS:  =I5  ****HOWEVER, THE RESULT IN THE CELL IS:  ='I5'  (NOTICE HOW IT DIFFERS)

    
    Range("AB5").Select
    ActiveCell.FormulaR1C1 = "=SUM(Z5+AA5)/730"
    'THE ABOVE ACTUAL CELL FORMULA IS:  =SUM(Z5+AA5)/730 ****HOWEVER, THE RESULT IN THE CELL IS:  =SUM('Z5'+'AA5')/730  (NOTICE HOW IT DIFFERS)


    
    Range("AC5").Select
    ActiveCell.FormulaR1C1 = "=A5"
    'THE ABOVE ACTUAL CELL FORMULA IS:  =A5  ****HOWEVER, THE RESULT IN THE CELL IS:  ='A5'  (NOTICE HOW IT DIFFERS)
 


    Range("X5:AC5").Select
    Range("X5:AC5").AutoFill Destination:=Range("X5:AC" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault

Chris,

What do you want to do?

FarmerScott

'-----------------------------------------------------------
Range("X4").Select
ActiveCell.FormulaR1C1 = "?????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =MID(A5,5,9)

Range("Y4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =COUNTIF(A:A, A5)>1

Range("Z4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =G5

Range("AA4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =I5

Range("AB4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =SUM(Z5+AA5)/730

Range("AC4").Select
ActiveCell.FormulaR1C1 = "??????"
'>>>>THE ABOVE ACTUAL CELL FORMULA IS: =A5)

End Sub

 

[farmerscott]

Chris,

Your formulas are erroring because you have a mismatch of syntax.

Firstly you declare that the formulas that are going into the cells are in the form of R1C1 but you have them as 'A1' style.

Alternatively, you can use

Range("X4").Formula =MID(A5,5,9)

Note that this way VBA will do the calculation and paste the result to the cell.

Pasting the formula to the cell or just the result is a matter of preference (and time if the code does a lot of pasting to cells). (See the following discussion on speed http://www.mrexcel.com/forum/excel-...p-visual-basic-applications-code-vlookup.html.)

If you are looking to copy a cell value into another you could use

Range("Z5").Value= Range("G5").Value

Hope that helps.

FarmerScott