rjplante
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 572
- Office Version
- 365
- Platform
- Windows
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?
Thanks for the help
VBA Code:
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