File attached
File attached
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I actually had a use for this a couple of days ago, Malcolm. I was actually trying to get the column letters in the worksheets and ended up using the CODE and CHAR functions to work out where I needed to be.
i.e. =CHAR(COLUMN()+64)
The above only works up to Column Z, of course, then you have to fool with it again. I like your method better.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
How simple you make that look!! I went the long way around to get the integers.
I was answering a question for someone on another forum the other day. The OP was going in all sorts of directions, but the long and short of the situation was that they were retrieving data and pasting it into a worksheet range that may or may not be the same every time.
I farted around with a couple of functions that the OP seemed to be elated with. In any case, here is the code and an example workbook to play with for anyone interested.
The Test It button simply gets a random range for me to test the functions with. I left it in for the OP to be able to see if it was going to fit the purpose he needed.Option Explicit Sub Test() Dim Reply As String Dim Temp As Range Dim TheCell As Range Dim FinalAnswer As String Randomize Set TheCell = Cells(4, Range("MyRandom")) Set Temp = Range(TheCell, TheCell.Offset(0, Range("MyRandom").Value)) Temp.Select Reply = Temp.Address FinalAnswer = ParseAddy(Reply, False) FinalAnswer = ParseAddy(Reply, True) Calculate End Sub Public Function AlphaDec(AlDec As String) As Double ' Designed to work for base 26 alphabet numbering system like Excel Column names Dim i As Long Dim j As Variant Dim k As Long Dim n As Long Dim AlphaDecArray() As Double n = Len(AlDec) k = -1 ReDim AlphaDecArray(1 To n) For i = n To 1 Step -1 j = Mid(AlDec, i, 1) k = k + 1 Select Case j Case Is = "A" AlphaDecArray(i) = 1 * 26 ^ (k) Case Is = "B" AlphaDecArray(i) = 2 * 26 ^ (k) Case Is = "C" AlphaDecArray(i) = 3 * 26 ^ (k) Case Is = "D" AlphaDecArray(i) = 4 * 26 ^ (k) Case Is = "E" AlphaDecArray(i) = 5 * 26 ^ (k) Case Is = "F" AlphaDecArray(i) = 6 * 26 ^ (k) Case Is = "G" AlphaDecArray(i) = 7 * 26 ^ (k) Case Is = "H" AlphaDecArray(i) = 8 * 26 ^ (k) Case Is = "I" AlphaDecArray(i) = 9 * 26 ^ (k) Case Is = "J" AlphaDecArray(i) = 10 * 26 ^ (k) Case Is = "K" AlphaDecArray(i) = 11 * 26 ^ (k) Case Is = "L" AlphaDecArray(i) = 12 * 26 ^ (k) Case Is = "M" AlphaDecArray(i) = 13 * 26 ^ (k) Case Is = "N" AlphaDecArray(i) = 14 * 26 ^ (k) Case Is = "O" AlphaDecArray(i) = 15 * 26 ^ (k) Case Is = "P" AlphaDecArray(i) = 16 * 26 ^ (k) Case Is = "Q" AlphaDecArray(i) = 17 * 26 ^ (k) Case Is = "R" AlphaDecArray(i) = 18 * 26 ^ (k) Case Is = "S" AlphaDecArray(i) = 19 * 26 ^ (k) Case Is = "T" AlphaDecArray(i) = 20 * 26 ^ (k) Case Is = "U" AlphaDecArray(i) = 21 * 26 ^ (k) Case Is = "V" AlphaDecArray(i) = 22 * 26 ^ (k) Case Is = "W" AlphaDecArray(i) = 23 * 26 ^ (k) Case Is = "X" AlphaDecArray(i) = 24 * 26 ^ (k) Case Is = "Y" AlphaDecArray(i) = 25 * 26 ^ (k) Case Is = "Z" AlphaDecArray(i) = 26 * 26 ^ (k) End Select Next i AlphaDec = Application.WorksheetFunction.Sum(AlphaDecArray) End Function Public Function ParseAddy(MyAddy As String, LeftOrRight As Boolean) As Integer ' If Left Argument is true, returns MyLefty, ' If Left Argument is false, returns MyRighty Dim MyLefty As String Dim MyRighty As String Dim NewLefty As Integer Dim NewRighty As Integer Dim TheColon As Integer MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "$", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "1", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "2", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "3", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "4", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "5", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "6", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "7", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "8", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "9", "") MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "0", "") TheColon = Application.WorksheetFunction.Find(":", MyAddy) ' MsgBox ("The Colon is in position #" & TheColon) MyLefty = Left(MyAddy, TheColon - 1) MyRighty = Right(MyAddy, Len(MyAddy) - TheColon) Select Case LeftOrRight Case Is = True Or 1 NewLefty = AlphaDec(MyLefty) ParseAddy = NewLefty Case Is = False Or 0 NewRighty = AlphaDec(MyRighty) ParseAddy = NewRighty End Select MsgBox (NewLefty & " " & NewRighty) End Function
Regards,
Brandtrock
How about thisOriginally Posted by Ken Puls
=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND("$",ADDRESS(ROW(),COLUMN(),2),1)-1)
This is what I use in VBA
[vba]
'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
[/vba]
This is actually Cyberdudes code from a potential KB item, posted here for him to access.
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I use this in Access to convert field names that represent cell addresses for transferring carefull crafted queries into reporting templates. http://vbaexpress.com/kb/getarticle.php?kb_id=838
[vba]Public Function GetRowColumn(strRowColumn) As RowColumn
Dim lngCount As Long
Dim lngChar As String
'function to get cell references out of column names where the column names
' are equivalent to A1 style cell references (Note:R1C1 references won't work with this)
For lngCount = 1 To Len(strRowColumn)
lngChar = Asc(Mid(strRowColumn, lngCount, 1))
If lngChar >= Asc("0") And lngChar <= Asc("9") Then
Exit For
End If
Next lngCount
'this extracts the column and row from the Field Name in the recordset.
GetRowColumn.col = Left(strRowColumn, lngCount - 1)
GetRowColumn.row = Right(strRowColumn, Len(strRowColumn) - lngCount + 1)
End Function
Option Explicit
Public Type RowColumn 'function requires a user defined type
row As String
col As String
End Type
[/vba]
But this is for parsing a would be Access column Name (which represent it's intended location in Excel) from Access to Excel.
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
This is what I use when I need a column letter:
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
GiveColLeter Target.Column
End Sub
Public Function GiveColLeter(iNumber As Long) As String
Dim A As String, B As String
If iNumber \ 26 > 0 Then A = Chr(64 + iNumber \ 26)
B = Chr(64 + ((iNumber / 26) - (iNumber \ 26)) * 26)
GiveColLeter = A & B
End Function
[/VBA]
a few more pennies on the stack
This is fantastic! What a great variety of ways!
I have to say that I find it pretty funny on those occasions that I write 50 lines of painstaking code, only to have Bob come along and give a 50 character formula to do the same thing.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
In the immortal words of Buzz Lightyear, "You're mocking me, aren't you?!"Originally Posted by Ken Puls
Ken, I know what you mean.
Brandtrock
I've already added it to my list (got 7 short ones now)Originally Posted by Ken Puls
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
I use something similar to Bob's, although it works with 97 as well...
[vba]Function ColLet(lngCol As Long) As String
'---------------------------------------------------------------------------------------
' Procedure : ColLet
' DateTime : 6/2006
' Author : Zack Barresse
' Purpose : Return a column letter from column index value. 97 Compliant
'---------------------------------------------------------------------------------------
Dim strAddy As String
ColLet = "ERROR!"
On Error GoTo 0
strAddy = Cells(1, lngCol).Address(False, False)
If Err <> 0 Or strAddy = "" Then Exit Function
ColLet = Left(strAddy, Len(strAddy) - 1)
End Function[/vba]
Of course, the downside, if there is not an activesheet, it fails.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
So does mine!Originally Posted by firefytr
Downside of using built-ins.Originally Posted by firefytr
8...
[VBA]
Sub ColumnLetter3()
Dim Addy As String
Addy = ActiveCell.EntireColumn.Address(0, 0)
MsgBox Left(Addy, Int(Len(Addy) / 2))
End Sub
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
The Split() function works in 97??Originally Posted by xld
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
No....Originally Posted by firefytr
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
...
I'm trying to understand how Bob's function works in 97, as I do not have it installed to test it. Bob, Ken, anyone mind filling in the gaps for me?
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
[vba]
#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional Delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues
If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & """}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next
Split = aryValues
End Function
#End If
[/vba]
Bob,
Not to split hairs, but doesn't the conditional compilation mean that it is essentially ignored in 97? I'd hardly say that qualifies as "works".
Let me know if I'm missing something...
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
I see now. Yeah, technically it will work in 97 through the use of a UDF to simulate the use of the (post 97) native Split() function. I should have said my function will work - natively - from 97 and up.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables