Using variable sized range in CountIf()

[paulquinlan100]

Hi

Im trying to insert a formula at the bottom of set of data that has
just been inserted into a worksheet, the amount of data inserted can
vary everytime the program is run so the range will vary. Im using the
following code:

Dim rngFormula As Range
rngFormula = Range(Cells(5, 4), Cells(RowNum - 1, 4))
Worksheets("07_08 New Stores").Cells(RowNum, 4).Formula = "=COUNTIF("
& rngFormula & "," & """û""" & ")"

However, i get a "Type mismatch" error. Any ideas where im going
wrong?

Thanks for any suggestions
Paul

 

[Zone]

This should be better to set up your formula:

Dim rngFormula As String
rngFormula = Range(Cells(5, 4), Cells(RowNum-1, 4)).Address

James
Hi

Im trying to insert a formula at the bottom of set of data that has
just been inserted into a worksheet, the amount of data inserted can
vary everytime the program is run so the range will vary. Im using the
following code:

Dim rngFormula As Range
rngFormula = Range(Cells(5, 4), Cells(RowNum - 1, 4))
Worksheets("07_08 New Stores").Cells(RowNum, 4).Formula = "=COUNTIF("
& rngFormula & "," & """û""" & ")"

However, i get a "Type mismatch" error. Any ideas where im going
wrong?

Thanks for any suggestions
Paul