Monika,

I was suggesting, without really saying, that you don't need the letter Y,
you should be able to work with column numbers. There is probably a good
reason, but why do you think you need the letter?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
hi bob

thanks for the response

no this doesn't work...
it fails at Range....giving an error at range.

I wasn't very clear b4...
taking a simple example...if i try and find ...location
Set PLColFind = Cells.Find("location")
i find at address $y$2...
i want to extract this Y from this address.... like if i want to know the
column number i can find by PLColFind.Column...it will give me digit 22

but what if i want to extract the "Y"???

thanks
Monika
"Bob Phillips" wrote in message
...
Monika,

Try something like

Range("B2",Cells(2, Range"Location").Row))

and stop selecting<Vbg! These 3 lines

Range("BE2").Select
Selection.NumberFormat = "#,##0"
Selection.Copy

can be written as

With Range("BE2")
.NumberFormat = "#,##0"
.Copy
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
hi...

i normally get to face problems where i need to extract the column
alphabets...

like an eg of my code:
Range("BE2").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-20]<0,RC[-1]/(RC[-20]/100),RC[-1]/0.94)"
Range("BE2").Select
Selection.NumberFormat = "#,##0"
Selection.Copy

here is another example:

Range("Y2").Select
ActiveCell.FormulaR1C1 = _




"=IF(OR(RC[-1]=""SI"",RC[-1]=""BI""),""Inhouse"",IF(RC[-1]=""MS"",""MAL"",IF
(OR(RC[-1]=""WR"",RC[-1]=""WQ""),""IFWS"",""Subcon"")))"
Range("Y2").Select
Selection.Copy
Range("Y3:Y" & Format(lastCellNum)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


I dont wan t to refer to these columns as BE...or Y2... its hard
coding.
This particular column Y2 has the heading of LOCATION. So i can find
out
the
column through the address...address returns me $Y$2....but how do i
extract the Y from it???

thanks in advance
monika