VBA Dynamic Range Formula NOT Set in Range

Question

I have a problem with my code. I run a simple VBA code with userform and in one of the workbook's worksheet I run my formula in specific range in specific columns. Somehow, and I dont know why, the code runs on this lines without some error but the cells stay as it was without formula when the code ends. If someone could analyze me problem here in columns AC and AD i will be greatful.

here is the specific lines of code, the problem is Just with columns AC, AD:

    VLR = MainWB.Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row

With MainWB.Worksheets(5)
    UniqueLRow = .Cells(Rows.Count, "E").End(xlUp).Row
End With

'Column AC
With MainWB.Worksheets(2)
    .Range("AC2").Activate
    .Range("AC2:AC" & VLR).FormulaR1C1 = "=IF(COUNTIF(Unique!R2C5:R " & UniqueLRow & " C5,visual!RC[-2]),1,"""")"
End With

HourLR = MainWB.Worksheets(6).Cells(Rows.Count, "E").End(xlUp).Row

'Column AD
With MainWB.Worksheets(2)
    .Range("AD2:AD" & VLR).FormulaR1C1 = "=IF(COUNTIF(HourCommissions!R2C5:R " & HourLR & " C5,visual!RC[-3])>0,1,"""")"
End With

Did you try to debug code using F8 and step through it. It will be good to add .select for seeing what it is executing. — shrivallabha.redij
Are you using On Error Resume Next somewhere before the code you showed in the description? — Subodh Tiwari sktneer

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-12-21T10:39:03

Give this a try and see if that resolves your issue...

With MainWB.Worksheets(2)
    .Range("AC2", "AC" & VLR).FormulaR1C1 = "=IF(COUNTIF(Unique!R2C5:R" & UniqueLRow & "C5,visual!RC[-2]),1,"""")"
End With

VBA Dynamic Range Formula NOT Set in Range

2 Answers