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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> =COLUMN(AA:AA)
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "daniel chen" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > =COLUMN(AA:AA)
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "daniel chen" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > =COLUMN(AA:AA)
>> >
>> > --
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> >
>> > "daniel chen" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:%[email protected]...
>>> > =COLUMN(AA:AA)
>>> >
>>> > --
>>> > Regards,
>>> >
>>> > Peo Sjoblom
>>> >
>>> >
>>> > "daniel chen" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >>> news:%[email protected]...
> >>> > =COLUMN(AA:AA)
> >>> >
> >>> > --
> >>> > Regards,
> >>> >
> >>> > Peo Sjoblom
> >>> >
> >>> >
> >>> > "daniel chen" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >>> news:%[email protected]...
>> >>> > =COLUMN(AA:AA)
>> >>> >
>> >>> > --
>> >>> > Regards,
>> >>> >
>> >>> > Peo Sjoblom
>> >>> >
>> >>> >
>> >>> > "daniel chen" <[email protected]> wrote in message
>> >>> > news:[email protected]...
>> >>> >> 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