replacing column alphabet
 

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

replacing column alphabet
 

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

replacing column alphabet
 

There are many answers, you could use activecell.offset(0,-
x) where x is the number of columns to the left you wish
to select,

OR you could get the cell address using the following 2
functions (and example sub-routine) to return the column
reference in the variable c.

I have used these for years (they come from Ole P
Erlandsen's website.

Option Explicit

'The function below converts an integer between 1 and 256
to a column reference between A and IV:

Function ColNo2ColRef(colno As Integer) As String
If colno < 1 Or colno 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, colno).Address(True, False,
xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1,
ColNo2ColRef, "$") - 1)
End Function


'The function below converts a column reference (A - IV)
to a column number between 1 and 256:

Function ColRef2ColNo(ColRef As String) As Integer
ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = Range(ColRef & "1").Column
End Function

Sub b()
Dim x As String
Dim b As Integer
Dim c As String

x = ActiveCell.AddressLocal
b = ColRef2ColNo(x)
c = ColNo2ColRef(b)

End Sub

-----Original 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




.

replacing column alphabet
 

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

replacing column alphabet
 

set rng = Range("y2")
letter = Left(rng(1).Address(0,0),1 - (rng(1).column 26))

testing in the immediate window for Y2, Z2, AA2

set rng = Range("AA2")
letter = Left(rng(1).Address(0,0),1 - (rng(1).column 26))
? letter
AA
set rng = Range("Z2")
letter = Left(rng(1).Address(0,0),1 - (rng(1).column 26))
? letter
Z
set rng = Range("Y2")
letter = Left(rng(1).Address(0,0),1 - (rng(1).column 26))
? letter
Y

--
Regards,
Tom Ogilvy

"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

replacing column alphabet
 

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