replacing column alphabet

  • Thread starter Thread starter monika
  • Start date Start date
M

monika

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
 
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)
 
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
 
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 said:
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 said:
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 = _
Click to expand...
"=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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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 = _
Click to expand...
Click to expand...
"=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
Click to expand...
out
the
column through the address...address returns me $Y$2....but how do i
extract the Y from it???

thanks in advance
monika
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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 = _
Click to expand...
Click to expand...
"=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
Click to expand...
out
the
column through the address...address returns me $Y$2....but how do i
extract the Y from it???

thanks in advance
monika
Click to expand...
Click to expand...
Click to expand...
 
Back
Top