I'm writing a code that counts the number of cells with the words "no item placed" in a column. I'm also having the code count the total number of cells in the column. Then to have it calculate the percent of unplaced cells. In the end, the code should write a statement in the second row of each column that says "X unplaced cells, X %". X = count/percent. the code works until the line NumUnplaced.Value = Count + " unplaced items, " + percentUnplaced + "%" where I get a type mismatch error. I think it's because NumUnplaced is a Range and the other two our data values. Any ideas on how to fix this?
Dim NumUnplaced As Range
Dim Count As Integer
Dim NumColumns As Integer
Dim colRange As Range
Dim NumRows As Integer
Dim percentUnplaced As Single
Set colRange = ActiveSheet.UsedRange
NumColumns = colRange.Columns.Count
NumRows = colRange.Rows.Count - 1
For i = 2 To NumColumns
Count = Application.WorksheetFunction.CountIf(Columns(i), "Item Not Placed")
percentUnplaced = Count / NumRows * 100
Set NumUnplaced = ActiveSheet.Cells(2, i)
NumUnplaced.Value = Count + " unplaced items, " + percentUnplaced + "%"
Next i
Bookmarks