Syntax Problem Entering Formula in Cells 2

[Omnicube]

Hello VBA Wizards,

I was hoping someone can help me with the an issue I am having with my VBA.

I am running the following script:

Sub FillRange()
    
Dim RowCount As Integer

Range("E2").Select
RowCount = Range(Selection, Selection.End(xlDown)).Count
    
    Num = "=IF(OR(H2=" & "Closed" & ",H2=" & "Cancelled" & "),1,0)"
    For Row = 2 To RowCount
        For Col = 3 To 3
            Sheets("Exception File").Cells(Row, Col).Formula = Num
        Next Col
     Next Row

End Sub

But it is entering =IF(OR(H2=Closed,H2=Cancelled),1,0) in the cell as opposed to entering quotes around Closed and Cancelled. Any ideas on how I can fix this?

 

[SkipVought]

hi

Num = "=IF(OR(H2=""Closed"",H2=""Cancelled""),1,0)"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Omnicube]

Thanks Skip!

Silly me... I need to increment the formula as well.
As the row increments, I need the correspondning value is column H to be read.

So C3 needs to read H3, C4 to H4 etc.

Any ideas on how I can do this?

 

[SkipVought]

Sub FillRange()
    With Range("E2")
        Range(.Cells, .Cells.End(xlDown)).Formula = "=IF(OR(H2=""Closed"",H2=""Cancelled""),1,0)"
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Omnicube]

Works like a charm! Thanks Skip.

If I don't want it to extend down to the millionth row, can I add a limit like I did with the row count script in my first example?

 

[Omnicube]

I got it figured out! Thanks again Skip!

 

[SkipVought]

If you have more than 1 row of data, my formula will work AS POSTED, and will NOT "extend down to the millionth row"!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}