How To Convert Column Number To Alphabet

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

Dim colNo As Long
colNo = 2

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

Sub Subtotal_3_9_GND()

    Dim LastCol As Integer
    Dim SubNo As Long
  
     
    If WorksheetFunction.CountA(Cells) > 0 Then
         
        LastCol = Cells.Find(What:="*", after:=[A1], _
                           SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column
        derlig = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        For ColNo = 1 To LastCol
             
            
            If Cells(1, ColNo).Value = "NBV" Or Cells(1, ColNo).Value = "GAV" Or Cells(1, ColNo).Value = "DEPREC" Or Cells(1, ColNo).Value = "AMOUNT" Then
                 
                SubNo = 9
            Else
                 
                SubNo = 3
            End If
            Cells(derlig + 1, ColNo).Formula = _
            "=SUBTOTAL(" & SubNo & "," & Cells(2, ColNo).Address & ":" & Cells(derlig, ColNo).Address & ")"
        
    Next ColNo
End If
End Sub

Sub Select_Entire_Row()
Dim RowNo As Integer
Dim ColNo As Integer
RowNo = Selection.Row
ColNo = Selection.Column
    If RowNo.Value >= 1 Then
        Cells(RowNo, ColNo).EntireRow.Select ' I want it to do both not one then the other
        Cells(RowNo, ColNo).EntireColumn.Select
    End If

End Sub

I have this code to delete any row where " C" are the first two characters in column F but it doesnt seem to work any ideas

Dim NumRows As Long
Dim RowNo As Double
Dim ColNo As Integer

Worksheets("Profile Report").Select

NumRows = Cells(65000, 5).End(xlUp).Row

For ColNo = 5 To 5

For RowNo = NumRows To 1 Step -1

If Left(Cells(RowNo, ColNo), 2) = " C" Then

Cells(RowNo, ColNo).EntireRow.Delete Shift:=xlUp

End If

Next RowNo

Next ColNo

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.

Hello, thanks for reading this
I am trying to write a code that will activate when I double click on a cell. I have managed to do this bit.

I then want the code to use the contents of the cell I have double-clicked as the search criteria.

The search is then to look for this value on another sheet.

I have got a code that looks for the value in a specific column - but I need one to do it by row, and I do not know how to modify the code to allow this.

The code I have is below. The code is looking for the SiteName, then going to the sheet for that site, then looking at which column has been selected (1 to 20), and finding that metric (AAAA to DDDD) on the sites sheet and selecting the cell.

I need to do the same thing except in my sites sheet, all the metric names are in column 3 and the values are all in column 5:

Please can you help - let me know if you need more information.

Thanks in advance

David


Private Sub worksheet_beforedoubleclick(ByVal Tgt As Range, Cancel As Boolean)
Dim SiteName, LineName As String
Dim SrchCol, SrchStr As String
Dim RowNo, ColNo, RowOffset As Integer

'If there is a line name in column 3 then jump to that sites sheet
If Cells(Tgt.Row, 3) "" And Tgt.Column < 21 Then
LineName = Cells(Tgt.Row, 3)

If Cells(Tgt.Row, 2) = "" Then
SiteName = Cells(Tgt.Row - 1, 2)
Else
SiteName = Cells(Tgt.Row, 2)
End If
'Define what to search for to move to the specific cell for a meaure
Select Case Tgt.Column
Case 3 To 5
SrchCol = 2
SrchStr = LineName
ColNo = 6
RowOffset = 1
Case 6
SrchCol = 2
SrchStr = LineName
ColNo = 8
RowOffset = 1
Case 7
SrchCol = 2
SrchStr = LineName
ColNo = 6
RowOffset = 0
Case 8
SrchCol = 2
SrchStr = LineName
ColNo = 8
RowOffset = 0
Case 9, 10
SrchCol = 2
SrchStr = "AAAA"
ColNo = 6 + (Tgt.Column - 9) * 2
RowOffset = 0
Case 11, 12
SrchCol = 2
SrchStr = "BBBB"
ColNo = 6 + (Tgt.Column - 11) * 2
RowOffset = 0
Case 13, 14
SrchCol = 2
SrchStr = "CCCC"
ColNo = 6 + (Tgt.Column - 13) * 2
RowOffset = 0
Case 15, 16
SrchCol = 4
SrchStr = "DDDD"
ColNo = 6 + (Tgt.Column - 15) * 2
RowOffset = 0
Case 17, 18
SrchCol = 4
SrchStr = "EEEE"
ColNo = 6 + (Tgt.Column - 17) * 2
RowOffset = 0
Case 19, 20
SrchCol = 2
SrchStr = "FFFF"
ColNo = 6 + (Tgt.Column - 19) * 2
RowOffset = 0
End Select
'Find specific row in sheet
RowNo = 4
With Worksheets(SiteName)
While .Cells(RowNo, SrchCol) SrchStr And RowNo < 40
RowNo = RowNo + 1
Wend
End With

Worksheets(SiteName).Activate
Worksheets(SiteName).Cells(RowNo + RowOffset, ColNo).Select

Cancel = True
End If
End Sub

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,

Hello one and all, I understand that the column function returns the number position of the column instead of the alphabet as described in the Excel Help Documentation.

=COLUMN(C10) gives 3

However, the interesting question now is how do I return the alphabet "C" instead of "3".

Any help would be greatly appreciated.

Hello all,

I am having problems trying to figure out how to do a loop in excel that copys rows to another sheet if the is a blank cell in the row. The problem I am having is some rows have more than one blank cell so it is creating duplicates, column A has unique values but having issues building this into a loop the code I am using is.

NumRows = Cells(65000, 1).End(xlUp).Row 'count number of rows to look at on raw data sheet
For ColNo = 1 To 46 ' start count column loop
For RowNo = 1 To NumRows 'start count row loop

If Cells(RowNo, ColNo).Value = "" And Cells(RowNo, 21).Value "External" Then

Cells(RowNo, ColNo).EntireRow.Copy
Worksheets("Missing Data").Select
Cells(65000, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("Employee Data Report").Select

End If
Next RowNo 'next row loop
Next ColNo 'all rows completed move onto next column

Which works fine but is copying over the rows with more than one blank cell once for every blank cell.

any help would be great.

Thanks

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.

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

i found this old thread:
http://www.ozgrid.com/forum/showthre...t=68728&page=1

and looks pretty simple & efficient, so i've been using this simplified version of a problem:

VB:

 
Range(Cells(8, 3), Cells(8, 95)).Select 
 
With Application.WorksheetFunction 
    kk = .Max(Selection) 
    ColNo = .Match(kk, Selection) 
End With 

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



By the way, i'm looking for a column, not a row.

When it executes "kk", works fine, but when it executes ColNo, the result is a completly different cell, it's 2 cells before the last one, so the match function isn't working right.

The only explanation that i think of is that the data has several zeros. This is a problem, because the real data has several voids of numbers and even more zeros. It's all numerical data anyway.

Please some help to solve this problem with Match to find the value!!

Can this be done ?

In cell A1:A10 I have various words.

I am trying to convert them to a number assuming every letter = a number in sequence of the alphabet then is summed together. ie:

a=1, b=2, c=3, d=4 ............. z=26.

So the word Excel would equal 5+24+3+5+12 giving me the answer 49.

Any help appreciated

Hi Guys,

I have asked this before but it wasn't really necessary at the time, so i didn't push it, but now it would be REALLY handy.
Theres a few points to this, so I'll make it easy to read

1. I need to define a variable range of all the USED cells in a column (starting at row 2 as the row 1 is the header)

2. I need to do this for columns A through to P inclusively (all using the same lastrow reference as that of column A)

3. I need to call the range name by the contents of the header in that column (Row 1)

4. Some of the data in the header row (row 1) contains spaces - can we change this to an underscore for the range name only?

5. Does a range only work for the active worksheet? Because i will have multiple worksheets, all with the exact same information in row 1, but different numbers of rows (and different data as well), but i will need the range defined on each worksheet......

I've found the following code and thought it was it, but it only defines on 1 worksheet, and for some reason only defines ranges for columns 1 and 2. Also it defines the range as A1:A65535 - not the USED range.....
Can anyone help me with this?

Code:

Sub Ranges()

Sheets("Shee1").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"
            Exit Sub
        End If
         wb.Names.Add Name:=myName, RefersToR1C1:= _
             "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
        Next i
End Sub

And lastly (I promise) if anyone CAN help me with any of the above, would it be too much to ask to also make a note about what each section of the code actually does? Just so that i can understand it for future use and that way, i can actually learn something from it, instead of just utilising it..

Thanks so much all.

Really appreciate you even reading all of this

Hi,
I've Got this vb code which is superb,but what change do i have to make or modified so it does not assign letter like in Column"B" and "D" and also
its run without any error if the list have max rows(1048576).
thanks,i realy appreciate for any help.
Code:

Option Explicit
Sub GradeFromAtoWhat()
    Dim RowNo As Long
    
    Do While Left(WorksheetFunction.Trim(Cells(RowNo + 1, 1)), InStr(1, WorksheetFunction.Trim(Cells(RowNo + 1, 1)), " ")) * 1 = 1
        RowNo = RowNo + 1
    Loop
    
    GradeAtoWhatever Chr(RowNo + 64)
End Sub
Sub GradeAtoWhatever(strFinalLetter As String)
    Dim arrTemp As Variant, arrBoolean As Variant
    Dim LastRow As Long, RowNo As Long, LastCol As Long, ColNo As Long, IndexNo As Long
    On Error GoTo ResetApplication
    Application.ScreenUpdating = False
    
    strFinalLetter = UCase(strFinalLetter)
    For IndexNo = 3 To (Asc(strFinalLetter) - 62)
        Cells(1, IndexNo) = Chr(IndexNo + 62)
    Next
    ReDim arrBoolean(Asc(strFinalLetter) - 65)
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For RowNo = 1 To LastRow
        arrTemp = Split(WorksheetFunction.Trim(Range("A" & RowNo)))
        For IndexNo = 0 To UBound(arrBoolean)
            arrBoolean(IndexNo) = IsNumberInRange(Columns(IndexNo + 3), arrTemp)
        Next
        
        For IndexNo = 0 To UBound(arrBoolean)
            If Not arrBoolean(IndexNo) Then
                AppendToFoundList IndexNo + 3, arrTemp
                Range("B" & RowNo) = Cells(1, IndexNo + 3)
                Exit For
            End If
        Next
    Next
    
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    RowNo = 0
    For ColNo = 3 To LastCol
        LastRow = Cells(Rows.Count, ColNo).End(xlUp).Row
        If LastRow > RowNo Then RowNo = LastRow
    Next
    Range(Cells(1, 3), Cells(RowNo, LastCol)).Copy
    Cells(1, LastCol + 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
    Range(Columns(3), Columns(LastCol)).EntireColumn.Delete
    Range(Columns(3), Columns(ActiveSheet.UsedRange.Columns.Count)).ColumnWidth = 4
    Cells(1, 1).Select
    
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub
Sub AppendToFoundList(ColNo As Long, arrApend As Variant)
    Dim NextRow As Long, EndRow As Long
    
    NextRow = Cells(Rows.Count, ColNo).End(xlUp).Row + 1
    EndRow = NextRow + UBound(arrApend)
    Range(Cells(NextRow, ColNo), Cells(EndRow, ColNo)).Value = WorksheetFunction.Transpose(arrApend)
    
    Range(Cells(1, ColNo), Cells(EndRow, ColNo)).RemoveDuplicates Columns:=1, Header:=xlYes
    
End Sub
Function IsNumberInRange(rng As Range, ArrNos As Variant) As Boolean
    Dim n As Long
    Dim rngCheck As Range
    
    IsNumberInRange = False
    For n = 0 To UBound(ArrNos)
        Set rngCheck = rng.Find(ArrNos(n) * 1, , , xlWhole)
        If Not rngCheck Is Nothing Then
            IsNumberInRange = True
            Exit For
        End If
    Next
    
End Function

Sheet1

A B C D E F G H I J K L M N O P Q R S T 1 1 2 6 13 A A 1 2 6 13 3 5 10 15 4 7 11 14 8 9 12 16 2 1 3 12 14 B B 1 3 12 14 2 5 11 16 4 9 13 15 6 7 8 10 3 1 4 5 8 C C 1 4 5 8 2 3 7 9 6 11 12 15 10 13 14 16 4 1 7 15 16 D D 1 7 15 16 2 4 10 12 3 8 11 13 5 6 9 14 5 1 9 10 11 E E 1 9 10 11 2 8 14 15 3 4 6 16 5 7 12 13 6 2 3 7 9 C 7 2 4 10 12 D 8 2 5 11 16 B 9 2 8 14 15 E 10 3 4 6 16 E 11 3 5 10 15 A 12 3 8 11 13 D 13 4 7 11 14 A 14 4 9 13 15 B 15 5 6 9 14 D 16 5 7 12 13 E 17 6 7 8 10 B 18 6 11 12 15 C 19 8 9 12 16 A 20 10 13 14 16 C
Sezuh

Hi all.

I have an excel addin for the software that we use which can allow uploads from Excel to our system. This is invoked by clicking a button on the taskbar which then runs the VBA held in the new Excel template.
I have also developed some code to re-organise our data so that it can be uploaded correctly. I would like to run my code and then call the upload code but this is in a different project.
I thought I could get away with something like:
Code:

Project.Userform.Show

but this doesn't work.

Anyone know?

second problem:
In one of my forms the user should enter the last column of a range of data. I need to convert that column letter to its relevant number (i.e. A=1, B=2, C=3.....Z=26) because it will be used to set the limit of an array.
I thought that there was a simple excel function for this but I cant find anything.


Thanks in advance for both questions.

Regards,

Phil.

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?

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!

Hello

I have a column of approx 20 Countries. 2 columns along from this I have information of the type of alphabet some ( but not all) of the countries use. The columns either side of this are being used for various other bits of data.

When one of the countries is selected in a drop data validation down box a corresponding cell uses VLOOKUP to show the alphabet that is used.

My problem is, new countries are being added quite often, but the users are not inserting a complete row, only a cell for the new country, this then throws out the information in the corresponding alphabet cell.

My question is, can I use IF to search a range of cells, so if I created a list of only the countries that use one alphabet (call it country list a) and a list that contains countries that use another alphabet (country list b), then have something like:

=IF(country_input=country list a,"alphabet 1", IF(country_input=country list b,"alphabet 2", " ")) - I know this won't work as is, but soething like it would be good!

Excel would then search my lists and provide the required info and reduce the likely hood of the alphabet type becoming separated from its relevant country.

I hope I've explained it enough, if my rambling is confusing please let me know and I'll try and explain it better,

Thanks for any help you can give

CC

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?