PDA

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



sebas1102
05-21-2006, 09:17 PM
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?


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


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

sebas1102
05-21-2006, 09:19 PM
erm the original VBA was like this:

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

mdmackillop
05-22-2006, 12:55 AM
Hi Sebas.
Please use the VBA tags to format your code postings
Regards
MD

mdmackillop
05-22-2006, 01:09 AM
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.

Bob Phillips
05-22-2006, 01:51 AM
Surely, this bit is wrong

Columns ("StkCodeMatlFC:StkDescMatlFC ")

is that meant to be uysing named ranges?

sebas1102
05-22-2006, 08:29 PM
[/VBA]
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]

Bob Phillips
05-23-2006, 01:00 AM
Maybe try



range(range("StkCodeMatlFC"),range("StkDescMatlFC")).EntireColumn

mdmackillop
05-23-2006, 04:59 AM
It looks to me like you are assigning numeric values to some variables, not assigning ranges to range names.

sebas1102
05-23-2006, 07:09 PM
I did assign by using case like this:
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

But i finally got it right after 1 day .... stoopid me.
Range(Cells(3, StkDescProcQty), Cells(LRowProcQty, StkDescProcQty)).Select


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

mdmackillop
05-23-2006, 11:54 PM
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.