paste a formula in a cell with vba

[rjplante]

I have a table that has the number of columns defined when it is opened for the first time. Column A is always a reference column and the number of columns is entered into cell CF1 and starts in column B. If the user enters 8 columns, there will be nine columns to the table. Everything on the table works great and the data is entered as needed. Some rows will have a single cell with data in it, and others will have multiple cells containing data. I want to sort out the single entry rows and then move them to another sheet. I have added a cell on the outside of the table, skipping a column and pasting a formula in the cell. The problem I am having is that when the formula is in the cell is using absolute references (=COUNTIF($B$5:$I$5,$A$5))as opposed to general references (=COUNTIF(B5:I5,A5)). I want to be able to fill down to the last row with the formula so I cannot have any absolute references in the formula (at least not the row references). My VBA code to place the formula is listed below. How can I fix it to remove the absolute references?

Dim MoveCells As Long

Cells(5, Range("CF1") + 3).Select

MoveCells = Range("CF1").Value


'   Add formula to outside of table
ActiveCell.Formula = "=COUNTIF(" & ActiveCell.Offset(0, -(MoveCells + 1)).Address & ":" & ActiveCell.Offset(0, -2).Address & "," & ActiveCell.Offset(0, -(MoveCells + 2)).Address & ")"

Thanks for the help

 

[DanteAmor]

Try this:

ActiveCell.Formula = "=COUNTIF(" & ActiveCell.Offset(0, -(MoveCells + 1)).address(0, 0) & ":" & ActiveCell.Offset(0, -2).address(0, 0) & "," & ActiveCell.Offset(0, -(MoveCells + 2)).address(0, 0) & ")"

 

[rjplante]

Try this:

ActiveCell.Formula = "=COUNTIF(" & ActiveCell.Offset(0, -(MoveCells + 1)).address(0, 0) & ":" & ActiveCell.Offset(0, -2).address(0, 0) & "," & ActiveCell.Offset(0, -(MoveCells + 2)).address(0, 0) & ")"

Dante, you are an Excel wizard!

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.