Problem with absolute refrence!!!

[melewie]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have this formula in VBA.<o></o>
ActiveCell.FormulaR1C1 = _
"=COUNTBLANK('[Employee_Data_Report_TEST.xls]Missing Data'!R[1]C[1]:R" & NumRows & "C1)"<o></o>
<o></o>
This is how it appears on the worksheet =COUNTBLANK('Missing Data'!$A3:B$690)<o></o>
<o></o>
But I need it to be a relative reference so it can be dragged across.<o></o>
<o></o>
Any help is greatly appreciated,

Thank you.

 

[Andrew Poulsom]

That would be:

With ActiveCell
    .FormulaR1C1 = "=COUNTBLANK('[Employee_Data_Report_TEST.xls]Missing Data'!R[1]C[1]:R[" & NumRows - .Row & "]C1)"
End With

 

[melewie]

That would be:

With ActiveCell
    .FormulaR1C1 = "=COUNTBLANK('[Employee_Data_Report_TEST.xls]Missing Data'!R[1]C[1]:R[" & NumRows - .Row & "]C1)"
End With

Thats nearly got but it it still shows as.
=COUNTBLANK('Missing Data'!$A3:B690)

 

[Andrew Poulsom]

Do you want all relative?

    With ActiveCell
        .FormulaR1C1 = "=COUNTBLANK('Missing Data'!R[1]C[1]:R[" & NumRows - .Row & "]C)"
    End With

 

[melewie]

Do you want all relative?

    With ActiveCell
        .FormulaR1C1 = "=COUNTBLANK('Missing Data'!R[1]C[1]:R[" & NumRows - .Row & "]C)"
    End With

Yes.

Thank works perfectly.

Thank you.