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

 

[reberryjr]

Ahhh...the value of UVLR is 6034. Once the filter happens, I'm expecting the formula to be entered in Q3426:Q6034.

 

[Fluff]

In the code that you've posted this line is commented out

On Error resume Next

Is it actually commented out in the code you're running?

 

[MARK858]

So just to be sure when we test, rows 2 to 3426 are being hidden when you run the filter and you have put UV. in front of range?

Edit: don't miss Fluff's post at the same time as mine.

 

[reberryjr]

Here is the code that works. It works with the On Error Resume Next commented out, or not.

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
    UV.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

Here is the code that isn't working, with the UV in front of .Range

Sub IDDupHLMS()
Application.ScreenUpdating = False
Dim UV As Worksheet
Set UV = ThisWorkbook.Sheets("Unique_Volume")
If UV.FilterMode = True Then
    UV.ShowAllData
Else
End If
SortAsc UV, "Q1", "P1", "I1"
With UV
    On Error Resume Next
    .UsedRange.AutoFilter Field:=9, Operator:=xlFilterValues, Criteria1:=Array("HLMS")
End With
SortAsc UV, "B1"
UVLR = UV.Range("A" & Rows.Count).End(xlUp).Row
If UVLR > 1 Then
    UV.Range("Q2:Q" & UVLR).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(RC[-15]=R[-1]C[-15]),""Yes"",""No"")"
Else
End If
UV.ShowAllData
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Comment out the

On Error Resume Next

What happens if anything?

 

[reberryjr]

I get an application undefined error on this line...

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

 

[Fluff]

It would have helped if you had posted the non working code to begin with rather than the working code.
Your formula is wrong, remove the ) in red

"=IF(RC[-15]=R[-1]C[-15][COLOR=#ff0000])[/COLOR],""Yes"",""No"")"

 

[reberryjr]

Well crap! I completely spaced on that. My sincere apologies for the oversight. Removing that closing parenthesis appears to have worked. What I don't understand is, why wouldn't it have tried to insert the incorrect formula, resulting in an error on the spreadsheet? Then I would've known to go and review the formula more closely.

 

[MARK858]

Just a tip, if you are going to use error handling for a specific action (the only reason to have it) then disable it immediately after the action has passed.

With UV
    On Error Resume Next
    .UsedRange.AutoFilter Field:=9, Operator:=xlFilterValues, Criteria1:=Array("HLMS")
    [COLOR="#FF0000"]On Error Goto 0[/COLOR]
End With

 

[reberryjr]

Thanks for the tip!