A mysterious problem about ListObject

[Frederick Chow]

Hi all,

I have a problem about adding in-cell validation at the Insert Row of a list
object at run time.

To replicate:

1. Prepare a simple list with five items, e.g. Apple, Pears, Orange,
Bananas, Grape, etc, and name the range as "Products".

2. Run the following code to generate a list for each product (these list
should be on a separate worksheet):

' ********************************
Sub GenListObj()
Dim rngProduct As Range
Dim rngGrid As Range
Dim lstProductDetails As ListObject
Dim OutputRow As Long, OutputCol As Byte
Set rngProduct = Range("Products")

OutputRow = 1
OutputCol = 4
For Each rngGrid In rngProduct.Cells
Cells(OutputRow, OutputCol) = rngGrid.Value
OutputRow = OutputRow + 1
Cells(OutputRow, OutputCol) = "Quantity"
Cells(OutputRow, OutputCol + 1) = "Amount"
Set lstProductDetails = ActiveSheet.ListObjects.Add(xlSrcRange, _
Range(Cells(OutputRow,
OutputCol), Cells(OutputRow, OutputCol + 1)), _
, xlYes)
With lstProductDetails
.Name = "lst" & Replace(rngGrid.Value, " ", "")
.ShowTotals = True
'*** Problematic code begin
With .InsertRowRange.Cells(1).Validation
.Add Type:=xlValidateList, Formula1:="=Products"
End With
'*** Problematic code end
End With

OutputRow = OutputRow + 3
Next
End Sub
'*****************************************************************

I found that if at runtime if the cell pointer is located on D4, E4, D8, E8,
D12, E12, D16, E16 or D20, E20, then an run time error message will occur.
Otherwise the list, together with the in-cell drop down list, will populate
with no problems. Why is that? Please advise how to change the code.

Frederick Chow
Hong Kong