Column names

[daniel chen]

Is there any code to convert column(number) to column(letters), and V.V.
e.g. 27 for AA, AB for 28
Thanks

 

[Peo Sjoblom]

=COLUMN(AA:AA)

 

[daniel chen]

Hi, Peo
Great! Thanks. How about the other way?
i.e. 27 for AA

 

[Dave Peterson]

How about:

=SUBSTITUTE(ADDRESS(1,27,4),"1","")

 

[daniel chen]

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)

 

[daniel chen]

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

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]

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)"

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]

Hi Dave,
Truely grateful.
I am developing tools for analysis.
Try to conserve memories and time.