Formula Not Pasting Through VBA

[reberryjr]

I have the code below, which is almost an exact copy of code elsewhere in the same workbook; but for some reason, the formula isn't being entered into the workbook. I've stepped through the code, and every line works as expected, except for the formula entry. Thoughts?

Sub IDDupBKI()
Application.ScreenUpdating = False
Dim UV As Worksheet
Set UV = ThisWorkbook.Sheets("Unique_Volume")
If UV.FilterMode = True Then
    UV.ShowAllData
Else
End If
'Sorts the Unique Volume sheet by Delete, Month, Queue
SortAsc UV, "Q1", "P1", "I1"
With UV
    'On Error Resume Next
    .UsedRange.AutoFilter Field:=9, Operator:=xlFilterValues, Criteria1:=Array("BKI Holds", "BKI Issues")
End With
'Sorts the visible cells by Loan, Bucket 1, Bucket 2, Date, Calculated Time
SortAsc UV, "A1", "B1", "C1", "D1", "G1"
UVLR = UV.Range("A" & Rows.Count).End(xlUp).Row
If UVLR > 1 Then
    Range("Q2:Q" & UVLR).SpecialCells(xlCellTypeVisible).Value = "=IF(AND(RC[-8]=R[-1]C[-8],RC[-16]=R[-1]C[-16],RC[-15]=R[-1]C[-15],RC[-14]=R[-1]C[-14],RC[-13]=R[-1]C[-13],RC[-12]=R[-1]C[-12]),""Yes"",""No"")"
Else
End If
UV.Range("Q2:Q" & UVLR).Copy
    UV.Range("Q2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
UV.ShowAllData
Application.ScreenUpdating = True
End Sub

 

[MARK858]

What happens with the change in red below?

If UVLR > 1 Then
    [COLOR="#FF0000"]UV.[/COLOR]Range("Q2:Q" & UVLR).SpecialCells(xlCellTypeVisible).Value = "=IF(AND(RC[-8]=R[-1]C[-8],RC[-16]=R[-1]C[-16],RC[-15]=R[-1]C[-15],RC[-14]=R[-1]C[-14],RC[-13]=R[-1]C[-13],RC[-12]=R[-1]C[-12]),""Yes"",""No"")"
Else

 

[reberryjr]

Nothing happened. I don't have the UV in front of the .Range in the other code that is working either.

 

[Fluff]

Change .Value to .FormulaR1C1

 

[MARK858]

Nothing happened. I don't have the UV in front of the .Range in the other code that is working either.

It might do if it was within a With statement and if you had the . in front of the range (which it isn't and you don't in the code you posted).

If it isn't working with the amendment then what is the value of UVLR when you are expecting the formula to go in the worksheet as it goes in the worksheet fine when ran as a separate line.

EDIT: See Fluff's previous post and test (although it went in fine for me with .Value when I ran it as a separate line as I also thought that was the issue).

 

[reberryjr]

Change made, but the formula still didn't populate the cells.

 

[reberryjr]

I made the change that Fluff suggested, but it didn't yield any results.

 

[Fluff]

You haven't answered MARK858's question.
What is the value of UVLR

 

[reberryjr]

Sorry...didn't realize I didn't respond to that. @MARK858 if you're referring to the snippet below, the purpose is, if the filter runs, and there are no results, I don't want the rest of the code to act upon the header row. This is just how I learned to code for filtering and updating filtered ranges. If there's a more practical/effective way, I'm all ears.

If UVLR > 1 Then
    UV.Range("Q2:Q" & UVLR).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(RC[-15]=R[-1]C[-15]),""Yes"",""No"")"
Else
End If

 

[MARK858]

If UVLR > 1 Then

No I want the value of UVLR when you think the formula should be pasting. Put a breakpoint on the next line, run the code and hover your mouse over UVLR when it breaks.