Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To Convert Column Number To Alphabet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

how to convert column number to alphabet
Code:

 
ColNo = 3
 
Range(ColNo & "1").Select




View Answers     

Similar Excel Tutorials

How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...
Sort Data that Doesn't Have Headers in Descending Order in Excel
Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not h ...
Convert Scientific Notation to Numbers in Excel
How to convert scientific numbers to show their full amount; this method also allows you to retain the appearance o ...
Convert Numbers Stored as Text to Numbers in Excel
I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situation often happens when you ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics







Hi.
I have some code which runs by clicking a button in Sheet1.
I want the code to insert a new column in Sheet2 (using a variable 'colNo', which holds the column insert location as a number.)

I thought the following piece of code would do this okay:

Code:

Dim colNo As Long
colNo = 2

Sheets("Sheet2").Select
Columns(colNo).Select
Selection.Insert Shift:=xlToRight


. . . but it doesn't work.
I keep getting a '1004' error(?)

Does anyone know how to do this correctly?

Thanks in advance for your help.


I'm having a problem with this r1c1 formula:

Code:

wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & "R" & TK & ":" & "C" & Colno & "R" & frow & ")"


for reference, here are the variables cited:

Code:

frow = ws.Cells(TK, Colno).End(xlDown).Row

TK = Rowno + Offset

Const Colno = 1

Const rowno = 5

Const offset = 5





I've found some code at this site that provides details on getting columns widths set to make grid lines of a specific width in millimeters. Here's what it looks like.

Code:

Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
    If ColNo < 1 Or ColNo > 255 Then Exit Sub
    Application.ScreenUpdating = False
    w = Application.CentimetersToPoints(mmWidth / 10)
    While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
        Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
    Wend
    While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
        Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
    Wend
End Sub

Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
    If RowNo < 1 Or RowNo > 65536 Then Exit Sub
    Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub


Code:

Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
    If RowNo < 1 Or RowNo > 65536 Then Exit Sub
    Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub


Is there a way to modify this code so that it prompts me for the grid size in millimeters? For example, if I wanted to create graph paper with grid lines 1x1 cm, I could enter a 1 and have the column widths and row heights set accordingly. Appreciate any direction or insights.


I have inherited some error checking code which tries to determine the alphabetic character of the column of a cell which is empty. Can anyone please advise on how the line of code below with 'replace' in it works?

Code:

 
If ce.Value = Empty Or ce.Value = "" Then
            rowNo = ce.Row
            colNo = ce.Column
            colLetter = Replace(Cells(1, colNo).Address(1, 0), "$1", "")
end if


All help and suggestions are gratefully received


Sheet1 No Alphabet 1 X 2 Y 3 Z 4 X 5 Y 6 Z Befo Sheet2 Filter Alphabet: X Filter Alphabet: Y Filter Alphabet: Z After: Sheet2 Filter Alphabet: X No Alphabet 1 X 4 X Filter Alphabet: Y Filter Alphabet: Z

I want to insert my auto/advanced filter between two cells
what is the vba code,


Hi,

I have developed the macro code below but I'm not sure how to evolve it further to acheive my aims.

I've created a dynamic range based on last populated column and row in a sheet then I'm trying to use the number of rows and columns in the range to select another range:

Sub test()

Dim lastcol, lastcell As Integer
lastcell = Sheets("Source Sheet").Range("c2000").End(xlUp).Row
lastcol = Sheets("Source Sheet").Range("c1").End(xlToRight).Column
colno = Selection.Columns.Count
Sheets("Test").select
Range("Y5").Select
Range(ActiveCell, Cells(lastcell + Column + colno)).Select 'try to select Y5 to AT163
End Sub

So for example if:

Lastcell = row 163
Lastcol = column 22

Then in the test sheet I want to select the range: ("Y5:AT163"). I'm sure this is very easy to do but I'm struggling, hope someone can help!

Thanks,

FE


Hi guys,

Have been a regular visitor for some time, picking up some great tips and knowledge in my Excel endeavours; however I have a problem where I'm requiring some assistance. Hoping someone here can help?

I'm attempting to write a code that will set dynamic named ranges across rows using the name in column A (starting at A2, A3, A4 etc) as the defined name and continuing down an undefined number of rows until a blank is found.

I've previous written a similiar code to set dynamic named ranges for columns (below) that works well, but no luck so far.

Code:

Sheets("Agent Shifts").Select
 
        Dim wb As Workbook, ws As Worksheet
        Dim lrow As Long, lcol As Long, i As Long
        Dim myName As String, Start As String
 
        Const Rowno = 1
 
        Const Offset = 1
 
        Const Colno = 1
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet
 
        lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
        lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
        Start = Cells(Rowno, Colno).Address
 
            wb.Names.Add Name:="lcol", _
                 RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
            wb.Names.Add Name:="lrow", _
                 RefersToR1C1:="=COUNTA(C" & Colno & ")"
            wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
        For i = Colno To lcol
 
        myName = Replace(Cells(Rowno, i).Value, " ", "_")
        If myName = "" Then
 
            MsgBox "Fatal Error " & i & vbCrLf _
                   & "Please contact #### to repair code"
            Exit Sub
        End If
 
        wb.Names.Add Name:=myName, RefersToR1C1:= _
             "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
        Next i


Is anyone able to help or point me in the right direction?

Cheers.


For a few weeks now, I've been using a tiny VBA function that returns a given column of a given range:

Code:

Public Function ColPart(rng As Range, colno As Integer)

ColPart = rng.Columns(colno)

End Function


This is necessary because I've got a bunch of data tables, and I've been trying to use their range names where possible rather than their A1-style address. The ColPart function allows me to use SUMPRODUCT with these named ranges - for example:

Code:

=SUMPRODUCT(colpart(Reading_Data,3)*colpart(Reading_Data,5))


However, I'm hitting a problem when I try to access data contained in another workbook. For example, the following function fails:

Code:

=SUM(colpart('C:\billing work\Source Book.xls'!Stirling_Data,3))


On investigation, this is apparently because, when Excel passes a range from one workbook to another, it coerces it to array rather than leaving it as a range. Thus, my ColPart function is being passed a two-dimensional array rather than a range, which it is completely unequipped to handle.

Is there any way to coerce the array back to a range, or something like that, so that I can easily extract the column from it? And, as a matter of interest, does anyone know why Excel behaves in this crazy fashion?




looking for a formula that can be convert the number to the alpha .
example A=1 , B=2, C=3, D=4, E=5...
when i key in 34.21 , it will show CD.BD

thks in advance

Good Morning.

I have to convert alphanumeric codes into numbers where they fall in the alphabet example BOGG, 21577 or Black 2121311.

Thanks in advance Stephen!


Is anyone aware of a funtion to return 1 for A, 2 for B, etc?

Thanks!


How do I iterate through the alphabet? Let's say I want to use the Range() command, and want to do it a bunch of times and select a different letter for the column in a organized manner. How would I do that?


vb how to convert col name in the numerical format to alphabet

for example i have 94 when i convert it should be converted to CP

Please help


Hi all,

This is my first post. I tried to look around for a solution to this, but I'm not even sure what to search for because it is difficult to describe. The best I can do is give you an example.

-Let's say I have the alphabet in column A.
-A1 is a header that reads "ALPHABET"
-A2-A27 are the letters of the alphabet. "A,B,C,D, etc...."
-B1 is a header that reads "WHATEVER"
-Here is the tricky part to describe. In the B column, I have a subset of the A column. In other words, I have some of the letters of the alphabet, but not all of the letters of the alphabet. Also, I don't have any letters twice. Column B might have "D,N,Z,H,Y" but it won't have anything like "B,G,B,M,N,G" where letters are repeated.
- I want to sort the B column so that the letters of the alphabet I have will be on the same row as the letters in the A column. So, I in the B column I have "C,B,E" in "B2,B3,B4" respectively, after sorting column B, the letters will be on the same row as the matching letter in the A column. The C would be moved from B2 to B4 (not B3 because of the header).

Wow, does that make sense? In real life, I will be dumping file names into excel columns and doing this. I can't go into specifics, but I will get a list of files to download (with a script). There are always errors and problems with a few files, though. Once I am done, I dump a directory of the files I could download into a text file and move it to an excel column. The idea is to sort the files I could download against the original list and find out what Im missing. Again, hope this makes sense, but if my real world description doesn't then just stick to the alphabet example, lol.

Thanks a million!


Hi, im using a code to create dynamic named ranges. the code is as follows:

Code:

Sub tryone()
'
'
' Create Dynamic named ranges
'

'

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long

Dim myName As String, Start As String

Dim wsName As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 5

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset = 5

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

TK = Rowno + Offset

' On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(TK, Colno).End(xlDown).Row
Start = Cells(Rowno, Colno).Address


'replace blanks in worksheet names with underscore for the purposes of adding range names
wsName = ws.Name
wsName = Replace(wsName, " ", "_")

wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")"
wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & "_lrow," & wsName & "_lcol)"

For i = Colno To lcol
' if a column header contains space or other invalid character etc, replace with underscore
myName = Replace(Cells(Rowno, i).Value, "/", "_")
myName = Replace(myName, " ", "_")
myName = Replace(myName, "&", "_")
myName = Replace(myName, "(", "_")
myName = Replace(myName, ")", "_")
myName = Replace(myName, "?", "_")
myName = Replace(myName, "\", "_")

If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"

nexti:
Next i

On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames"

End Sub


At the beginning of the code, lrow is a variable that holds the number of rows i need later on in the index function (replicated here):

Code:

wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"


My problem is that lrow in the macro equals 75, but when it is used in the workbook it equals 68 for some reason, so my range is stopping 7 rows short!

Why is this the case? how do i change it?

This is the workbook:
http://www.mediafire.com/file/4e0mg3...9r/sample.xlsm


Hello,

I have the addresses of some cells written down in this format:

$G$12
$K$17
$Z$23
$P$28

i am looking for a custom function to convert the column Letters to numbers starting with letter "A" being number 1 etc... i.e. for the first example - if my custom function was used on $G$12, then I would like it to return the number 7, because G is the seventh letter in the alphabet.

is this possible??

thanks
andy


I keep getting a type mismatch error when trying to use the Instr() function. Could someone tell me why I get a type mismatch at Instr() in the following code. Note that I use two strings "timestamper" and "timestamp" to make SURE I'm comparing strings. (These strings are not what I plan to use in the code. They are used only for testing.)

Code:

 
Private Sub CommandButton2_Click()
Dim mytxt As String
colno = ListBox1.ListIndex + 1
icount = 0
For i = 1 To 65536
         mytxt = Worksheets("Responses").Cells(i, colno)
            If Not (IsEmpty(mytxt)) And InStr("timestamper", "timestamp", 1) > 0 Then
            timestamppieces = Split(myval, "@")
        End If
Next i

End Sub





I've been pulling my hair out over this issue and I cannot for the life of me figure out what's going on.

I have two sheets (Distances and Marketshare). The Distances sheet is a matrix of distances by miles between sets of zip codes. What I need to be able to do is find the MIN for each row and then record that mileage datum and the associated zip code (column header) to the Marketshare sheet.

I have everything working except for the fact that my fnd range object doesn't seem to be getting assigned any values. I'm wondering if this has anything to do with the fact that I'm trying to do things across two different sheets.

Code is below and I have also attached the xls file.

VB:

Sub enterMarketshareData() 
    Dim rowNum, numToAnalyze, numFacilities As Integer 
    Dim currentZip, caseZipRange, startZip As Range 
    Dim distanceRow As Range 
    Dim minValue As Double 
    Dim fnd As Range 
     
    Application.ScreenUpdating = False 
    Sheets("Marketshare").Activate 
     
    numToAnalyze = 3 
    numFacilities = 6 
     
     'enter onsheet equations
     
    Set currentZip = Sheets("Marketshare").Range("A2") 
     
    For I = 1 To numToAnalyze 
        Set startZip = currentZip.Offset(0, 1) 
         
         'find zip of rational facility for this zip
         
         'define the row range
        Set distanceRow = Sheets("Distances").Range("A" & I + 1, ColNo2ColRef(numFacilities + 1) & I + 1) 
        MsgBox "distanceRow is " & distanceRow.Address 
        minValue = Application.Min(distanceRow) 
        MsgBox "minValue is " & minValue 
        startZip.Value = "=min(Distances!" & distanceRow.Address(False, False) & ")" 
         'look up zip of closest facility
        Set startZip = startZip.Offset(0, 1) 
        Set distanceRow = Sheets("Distances").Range("A" & I + 1, ColNo2ColRef(numFacilities + 1) & I + 1) 
         
         ' XXXXXXXXXXX  trying to figure out why the below code never writes anything to the cell XXXXXXXXXX
        Set fnd = distanceRow.Find(what:=minValue, LookIn:=xlValues, LookAt:=xlWhole) 
        If Not fnd Is Nothing Then 
            startZip.Value = Range(ColNo2ColRef(fnd.Column) & 1).Value 
        End If 
         
         'Sheets("Marketshare").Activate
        Set currentZip = currentZip.Offset(1, 0) 
    Next I 
End Sub 
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 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Is is possible to add an alphabet to the duplicate value in the
selected range of cells.

Please guide......!


Regards
Thyagaraj




I can easily create a column of numbers that increase in increments of 1 in each successive cell by adding 1 to the cell above. The first cell is the only cell where I manually input a number. I want to set a column so that I can input a letter, say E, in the top cell and the cells below will fill in with F, G, H, etc. If this can be done, am I limited to a maximum of 26 cells to match the number of letters in the alphabet, or can I go into letters like AA, AB, AC, etc.


I am trying to convert a column of numbers into hours:minutes:seconds.

For example, I have following numbers (original data followed by desired output). I tried using the custom number format but could not get the desired result. Any assistance appreciated.

9 - convert to :09
53 - convert to :53
68 - convert to 1:08
109 - convert to 1:49
121 - convert to 2:01
262 - convert to 4:22


Hi all,

Hope you can help me cus i been everywhere and tried everything already.

i need to convert all the numbers in a sheet from text to number format so it would do the calculations.

however not all the columns are supposed to be formatted as numbers so i added a loop to identify which ones should be formatted as number.

But then on such columns i used the following code to convert text to number:

Code:




Code:

     
  Sub ConvertText2Num()  
     
  For Each xCell In Selection
   xCell.Value = CDec(xCell.Value)
Next xCell  
         
  End Sub  
     




It works great except i need it to convert the whole column except the first line cus tht one is the header.
And secondly it gives me an error if there are any cells that do have number formatting and does not continue formatting the other cells that do not have the number formatting.

Any advice to help me reach my objective would be greatly appreciated.

Thank you in advance,

Mayra


Can anyone tell me what to use where the question marks below are, to convert a number as a value into a number as text, i.e. convert 310.50 to '310.50. The number will be a different number each time.

Code:

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = ???????
    ActiveCell.Offset(1, 0).Range("A1").Select





Hi,

I am trying to get a function that will convert a string to a unique number. The function can return an error if more than 1 cell is selected and also if characters in the string are not letters, and does not need to be case sensitive.

The function would work like this:

=StringToNumber("Hello")
=0803121215

In that case it would be parsing through the string & replacing each letter with the # of that letter in the alphabet (A=01, etc.)

Ideally there is some kind of function in VBA to convert a letter to its ASCII number or something, which probably would mean it could support symbols & spaces but, again, thats not necessary.

Thanks,

Chris


This is driving me mad...

I have lots of cells that have the 'Background checking' green tag triangle thing on. Clicking on this says "Number stored as Text".

Now, I can manually click on this with no problems and convert it to a Number, but I can't seem to get these VBA codes to do it for me.

I have lots of columns which need to be numbers (not ALL columns though), and I have some columns which contains both numbers AND text.

Normally I highlight everything, press enter and click on the manual 'Convert to Number'.

This is what I have used so far, to no avail:

Code:

With ActiveSheet.Range("A:AA")
    .Select
    .NumberFormat = "0"
End With


Code:

With ActiveSheet.UsedRange
    .Value = .Value
End With


Please help, before I throw my laptop out of the window.

Regards,

James