|
Column names
Is there any code to convert column(number) to column(letters), and V.V.
e.g. 27 for AA, AB for 28 Thanks |
=COLUMN(AA:AA)
-- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks |
Hi, Peo
Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" wrote in message ... =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks |
How about:
=SUBSTITUTE(ADDRESS(1,27,4),"1","") daniel chen wrote: Hi, Peo Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" wrote in message ... =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks -- Dave Peterson |
Hi Dave,
Thank you for the code. I may be able to use it. I have this code Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)" And I want to make this constant range("$AH$8:$AN$200") a variable at the beginning of the setup. i.e. AH (which is col(34)) = Function(Cells(1, 10)) let's say Function1(cells(1, 10)) = cells(1, 10) + 29 Function2(cells(1, 10)) = cells(1, 10) + 35 If cells(1, 10) = 5 then, Function1(cells(1, 10)) = 34 which is column("AH") Function2(cells(1, 10)) = 40 which is column("AN") AH AN depend on what is in cells(1, 10) "Dave Peterson" wrote in message ... How about: =SUBSTITUTE(ADDRESS(1,27,4),"1","") daniel chen wrote: Hi, Peo Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" wrote in message ... =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks -- Dave Peterson |
Hi Dave,
I have it working, but I couldn't make the super formula to work. Why is that? Sub getdata() Dim r As Integer Dim c As Integer Dim LR As Integer Dim reg1 As Variant reg1 = Cells(1, 27).Value Range("AA2") = "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")" Range("AA3") = "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & ",4),""1"","""")" For LR = 1 To 200 If Cells(LR, 1) = "" Then Exit For Next LR For r = 1 To LR - 1 For c = 2 To 7 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$AH$8:$AN$200," & c & ",FALSE)" 'orginal Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & Range("AA2") & "$8:$" & Range("AA3") & "$200," & c & ",FALSE)" ' it works 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")" & "$8:$" & "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & ",4),""1"","""")" & "$200," & c & ",FALSE)" ' does not work Next c Next r Cells(1, 1).Select End Sub "daniel chen" wrote in message ... Hi Dave, Thank you for the code. I may be able to use it. I have this code Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)" And I want to make this constant range("$AH$8:$AN$200") a variable at the beginning of the setup. i.e. AH (which is col(34)) = Function(Cells(1, 10)) let's say Function1(cells(1, 10)) = cells(1, 10) + 29 Function2(cells(1, 10)) = cells(1, 10) + 35 If cells(1, 10) = 5 then, Function1(cells(1, 10)) = 34 which is column("AH") Function2(cells(1, 10)) = 40 which is column("AN") AH AN depend on what is in cells(1, 10) "Dave Peterson" wrote in message ... How about: =SUBSTITUTE(ADDRESS(1,27,4),"1","") daniel chen wrote: Hi, Peo Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" wrote in message ... =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks -- Dave Peterson |
I didn't realize you wanted to use a VBA routine...
dim reg1 as long dim myRng as range reg1 = activesheet.cells(1,27).value with sht1 set myrng = .range(.cells(1,reg1+29),.cells(200,30+2*reg1)) end with 'I'm confused about what you're doing, but maybe something like... activesheet.cells(r,c).formula _ = "=vlookup($A" & r & "," & myrng.address(external:=true) & "," _ & c & ",false)" daniel chen wrote: Hi Dave, I have it working, but I couldn't make the super formula to work. Why is that? Sub getdata() Dim r As Integer Dim c As Integer Dim LR As Integer Dim reg1 As Variant reg1 = Cells(1, 27).Value Range("AA2") = "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")" Range("AA3") = "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & ",4),""1"","""")" For LR = 1 To 200 If Cells(LR, 1) = "" Then Exit For Next LR For r = 1 To LR - 1 For c = 2 To 7 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$AH$8:$AN$200," & c & ",FALSE)" 'orginal Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & Range("AA2") & "$8:$" & Range("AA3") & "$200," & c & ",FALSE)" ' it works 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")" & "$8:$" & "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & ",4),""1"","""")" & "$200," & c & ",FALSE)" ' does not work Next c Next r Cells(1, 1).Select End Sub "daniel chen" wrote in message ... Hi Dave, Thank you for the code. I may be able to use it. I have this code Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)" And I want to make this constant range("$AH$8:$AN$200") a variable at the beginning of the setup. i.e. AH (which is col(34)) = Function(Cells(1, 10)) let's say Function1(cells(1, 10)) = cells(1, 10) + 29 Function2(cells(1, 10)) = cells(1, 10) + 35 If cells(1, 10) = 5 then, Function1(cells(1, 10)) = 34 which is column("AH") Function2(cells(1, 10)) = 40 which is column("AN") AH AN depend on what is in cells(1, 10) "Dave Peterson" wrote in message ... How about: =SUBSTITUTE(ADDRESS(1,27,4),"1","") daniel chen wrote: Hi, Peo Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" wrote in message ... =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks -- Dave Peterson -- Dave Peterson |
Hi Dave,
Truely grateful. I am developing tools for analysis. Try to conserve memories and time. "Dave Peterson" wrote in message ... I didn't realize you wanted to use a VBA routine... dim reg1 as long dim myRng as range reg1 = activesheet.cells(1,27).value with sht1 set myrng = .range(.cells(1,reg1+29),.cells(200,30+2*reg1)) end with 'I'm confused about what you're doing, but maybe something like... activesheet.cells(r,c).formula _ = "=vlookup($A" & r & "," & myrng.address(external:=true) & "," _ & c & ",false)" daniel chen wrote: Hi Dave, I have it working, but I couldn't make the super formula to work. Why is that? Sub getdata() Dim r As Integer Dim c As Integer Dim LR As Integer Dim reg1 As Variant reg1 = Cells(1, 27).Value Range("AA2") = "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")" Range("AA3") = "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & ",4),""1"","""")" For LR = 1 To 200 If Cells(LR, 1) = "" Then Exit For Next LR For r = 1 To LR - 1 For c = 2 To 7 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$AH$8:$AN$200," & c & ",FALSE)" 'orginal Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & Range("AA2") & "$8:$" & Range("AA3") & "$200," & c & ",FALSE)" ' it works 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")" & "$8:$" & "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & ",4),""1"","""")" & "$200," & c & ",FALSE)" ' does not work Next c Next r Cells(1, 1).Select End Sub "daniel chen" wrote in message ... Hi Dave, Thank you for the code. I may be able to use it. I have this code Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)" And I want to make this constant range("$AH$8:$AN$200") a variable at the beginning of the setup. i.e. AH (which is col(34)) = Function(Cells(1, 10)) let's say Function1(cells(1, 10)) = cells(1, 10) + 29 Function2(cells(1, 10)) = cells(1, 10) + 35 If cells(1, 10) = 5 then, Function1(cells(1, 10)) = 34 which is column("AH") Function2(cells(1, 10)) = 40 which is column("AN") AH AN depend on what is in cells(1, 10) "Dave Peterson" wrote in message ... How about: =SUBSTITUTE(ADDRESS(1,27,4),"1","") daniel chen wrote: Hi, Peo Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" wrote in message ... =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" wrote in message ... Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 08:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com