Hi all,
I've been using "hard coding" to refer to cells but got into a problem when i need to insert another column.

For example, if i refer to say Range("E1"), and then insert a row before it... VBA is pointing to the new Range("E1"), which used to be "D1". When you name a range, that doesn't happen since the named range is dynamic.

So what I did to try solving is by using case but error come up after I try changing the "hard coded" range to range names. What gives?

[VBA]
Windows("materialForecastnew.xls").Activate
FieldRowMatlFC = 2
LcolMatlFC = Cells(FieldRowMatlFC, 256).End(xlToLeft).Column
For iCol = 1 To LcolMatlFC
Item = Cells(FieldRowMatlFC, iCol)
Select Case Item
Case "s/n"
SNColMatlFC = iCol
Case "AJO"
AJOMatlFC = iCol
Case "QtnRef"
QtnRefMatlFC = iCol
Case "item"
ItemMatlFC = iCol
Case "stock code"
StkCodeMatlFC = iCol
Case "Stock Desc"
StkDescMatlFC = iCol
Case "sets"
SetsMatlFC = iCol
Case "stock unit quantity"
StkUnitQtyMatlFC = iCol
Case "total Qty"
TlQtyMatlFC = iCol
Case "ProdnStartDate"
ProdnSDateMatlFC = iCol
Case "week no"
WkNMatlFC = iCol
End Select
Next iCol
[/VBA]

The syntax error pops up here(i use record macro and filter to give the vba codes):
Columns("StkCodeMatlFC": "StkDescMatlFC").AdvancedFilter Action:=xlFilterInPlace, [VBA]
CriteriaRange:= _
Columns("StkCodeMatlFC:StkDescMatlFC "), Unique:=True"
Cells(3 & StkCodeMatlFC, LRowMatlFCN & StkDescMatlFC).Copy
[/VBA]