Solved: How to use Range Names in a correct way?

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]

erm the original VBA was like this:
[VBA]
sub original()
Windows("materialForecastnew.xls").Activate
LRowMatlFCN = Range("F2").End(xlDown).Row
Columns("E:F").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Columns("E:F"), Unique:=True
Range("E3", "F" & LRowMatlFCN).Copy
Windows("Procurement QtyNew.xls").Activate
Range("B3").Select
ActiveSheet.Paste
Windows("materialForecastnew.xls").Activate
ActiveSheet.ShowAllData
end sub
[/VBA]

Hi Sebas.
Please use the VBA tags to format your code postings
Regards
MD

For a start, you should declare your variables to ensure the correct data is being stored by them. Having done this, I think the problem lies in the line
Cells(3 & StkCodeMatlFC, LRowMatlFCN & StkDescMatlFC).Copy

"3 & StkCodeMatlFC" may not produce the figure which is required for your Cells address.

Surely, this bit is wrong

Columns ("StkCodeMatlFC:StkDescMatlFC ")

is that meant to be uysing named ranges?

[/VBA]

Quote:

---

Originally Posted by xld

Surely, this bit is wrong

Columns ("StkCodeMatlFC:StkDescMatlFC ")

is that meant to be uysing named ranges?

---

yes! But no matter how i change i get application-defined or object-defined error:(

this is the latest i tried( the comments are the correct ones) :
[VBA]
' Workbooks("MaterialForecastNew.xls").Sheets("Sheet1").Range("E2:F41"). _
' AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B2:C2"), Unique _
' :=True

Range(Cells(2, StkCodeMatlFC), Cells(2, StkDescMatlFC)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Columns(StkCodeMatlFC, StkDescMatlFC), Unique:=True

[VBA]

Maybe try

[vba]

range(range("StkCodeMatlFC"),range("StkDescMatlFC")).EntireColumn
[/vba]

It looks to me like you are assigning numeric values to some variables, not assigning ranges to range names.

I did assign by using case like this:
[VBA]Windows("materialForecastnew.xls").Activate
FieldRowMatlFC = 2
LcolMatlFC = Cells(FieldRowMatlFC, 256).End(xlToLeft).Column
For icol = 1 To LcolMatlFC
Item = UCase(Trim(Cells(FieldRowMatlFC, icol)))
Select Case Item
Case "S/NO"
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"
lQtyMatlFC = icol
Case "PRODNSTARTDATE"
ProdnSDateMatlFC = icol
Case "WEEK NO"
WkNMatlFC = icol
End Select
Next icol
LRowMatlFCN = Range("F2").End(xlDown).Row
End Sub
[/VBA]
But i finally got it right after 1 day .... stoopid me.
[VBA]Range(Cells(3, StkDescProcQty), Cells(LRowProcQty, StkDescProcQty)).Select
[/VBA]

anyway thanks XLD and mdmackillop! i will go try XLD's vba out!

This line sets LcolMatIFC to a number (Long)
LcolMatlFC = Cells(FieldRowMatlFC, 256).End(xlToLeft).Column

This line uses the previous assigned number in the loop, where icol must also have a numeric value
For icol = 1 To LcolMatlFC

This line assigns a numeric value (icol) to SNColMatIFC
SNColMatlFC = icol

I don't see where range names come into this.