Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change column from numeric to alphabet? | Setting up and Configuration of Excel | |||
Column heading is numeric. How to change to Alphabet? | Excel Discussion (Misc queries) | |||
format Column with horizontal alphabet label | Excel Discussion (Misc queries) | |||
autofill alphabet in column | New Users to Excel | |||
How can I fill in a column with the alphabet | Excel Discussion (Misc queries) |