Filtering a HeaderRowRange list

**LJMetzger** · 02-28-2015, 07:17 AM

Hi Scott,

Your test conditions are incorrect. Each test will always be true unless the Category Name is "-1". My test works as follows:
a. Look at the category name 'CbAllocate2.Value'
b. Remove leading and trailing spaces 'Trim'
c. If the number of characters remaining is greater than zero, it must be a category name
NOTE: If you type in a Category name that isn't in the list, the test will pass and you will get the same error that you had highlighted in yellow, because the 'CATEGORY DOES NOT EXIST' and there is no valid 'ListObjects' column.

If Len(Trim(CbAllocate2.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate2.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit2.Value
  End If

If Len(Trim(CbAllocate3.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate3.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit3.Value
  End If

-------------------
Your error isn't due to 'Option Explicit'. 'Option Explicit' saves a lot of headaches when you spell something wrong. As a test, comment out 'Option Explicit', and replace 'response ' with 'resp0nse ' (ZERO that looks a little like the letter 'o'). The logic is correct but the answer will always NOT be 'vbYes'. I run across errors like that all the time in other people's code, and in my code when 'Option Explicit' is accidentally erased. I once spent more than two weeks confused by a variable spelling error, when I knew the logic was correct.