How to programmatically (VBA macro) set the contents of a cell to be a formula ?

example for A11:A19

Range("a11:a19") = "=IF(NOT( ISBLANK(F11) ), IF(F11<E11,-(E11-F11),F11-E11), """")"

I tried the watersev suggestion and that does almost what I want.

I have this function below - that works - except of course for adding the formulas.

Public Sub Make_my_new_sheet()
    Dim InputSheet As Variant
    Dim r As Long, yOff As Long, rowOut As Long
    Dim s As String
    
    InputSheet = Sheets("Input").UsedRange
    yOff = 10       ' start pasting to row 10 of new sheet
    For r = 2 To UBound(InputSheet, 1) 'skip row 1
        rowOut = r - 1 + yOff
        s = InputSheet(r, 1)
        Sheets("Out").Cells(rowOut, 1) = s
           
        s = InputSheet(r, 2)
        Sheets("Out").Cells(rowOut, 2) = s
           
        s = InputSheet(r, 6)
        Sheets("Out").Cells(rowOut, 3) = s
           
        s = InputSheet(r, 7)
        Sheets("Out").Cells(rowOut, 4) = s
           
        s = InputSheet(r, 9)
        Sheets("Out").Cells(rowOut, 5) = s


        Sheets("Out").Cells(rowOut, 6) = ""        

        ' =======================================================       
        ' This below is what I want to do ...    
 
        ' Sheets("Out").Cells(rowOut, 7) =   =IF ( NOT( ISBLANK(F11) ), IF (F11<E11,-(E11-F11),F11-E11), "")"
        ' Sheets("Out").Cells(rowOut, 8) =   =IF ( NOT( ISBLANK(F11) ), ( ABS(E11-F11)/E11), "")
        ' Sheets("Out").Cells(rowOut, 9) =   =IF ( AND( NOT( ISBLANK(F11) ), (H11>$C$7)), "???", "")

        '  ... with the exception that every E11 & F11 & H11 would increment (to E12 & F12 & H12, E13 & F13 & H13)
        '      every  iteration of the loop.  
        ' =======================================================       
     
        Sheets("Out").Cells(rowOut, 10) = "" 
           
        s = InputSheet(r, 12)
        Sheets("Out").Cells(rowOut, 6 + 5) = s
           
        s = InputSheet(r, 14)
        Sheets("Out").Cells(rowOut, 7 + 5) = s
        Sheets("Out").Cells(rowOut, 8 + 5) = r - 1
    Next

End Sub

Thanks

Hi Rex. I recently had a similar issue about programatically putting formulas into ranges. I may be giving you an incorrect suggestion, but 2 points I found was that the cell recieving the formula sometimes (seemingly somtimes, not always) must be set to the General cell format. Also, tag on ".formula" to the code. IE.

Sheets("Out").Cells(rowOut, 7).Formula = "=IF ( NOT( ISBLANK(F11) ), IF (F11<E11,-(E11-F11),F11-E11), "")"
Sheets("Out").Cells(rowOut, 8).Formula = "=IF ( NOT( ISBLANK(F11) ), ( ABS(E11-F11)/E11), "")"
Sheets("Out").Cells(rowOut, 9).Formula = "=IF ( AND( NOT( ISBLANK(F11) ), (H11 > $C$7)), ""???"", "")"

I fixed your qoutes a little also, you had some errors there. Don't know if this fixes your problem for sure. Maybe?

@rex99

Put this instead of commented block in the posted code of yours

Sheets("Out").Cells(rowOut, 7) = "=IF(NOT(ISBLANK(F" & rowOut & ")),IF(F" & rowOut & "<E" & rowOut & ",-(E" & rowOut & "-F" & rowOut & "),F" & rowOut & "-E" & rowOut & "), """")"
        Sheets("Out").Cells(rowOut, 8) = "=IF(NOT(ISBLANK(F" & rowOut & ")),(ABS(E" & rowOut & "-F" & rowOut & ")/E" & rowOut & "), """")"
        Sheets("Out").Cells(rowOut, 9) = "=IF(AND(NOT(ISBLANK(F" & rowOut & ")),(H" & rowOut & ">$C$7)), ""???"", """")"

That worked - thanks