This is a discussion on Convert a character to its ASCII value within the Excel Questions forums, part of the Question Forums category; I thought (remembered?) it was easy to convert a char to ASCII using ASC() as follows: Col B is formatted ...
I thought (remembered?) it was easy to convert a char to ASCII using ASC() as follows:
Col B is formatted to type=Number
A1 holds "N"
B1 holds =ASC(A1) expected but got "N"
B1 holds =ASCII(A1) #NAME
I searched MRExcel and google for "Excel Convert Character to ASCII" and got no answer.
Anyone know the answer? Thanks.
Thanks, Rick. Ah, I'm revealing my background. I tried CODE("char") in VBA but threw error as below
MsgBox (Left(ActiveCell.Value, 1))
compile Error of "Sub Not defined"
MsgBox (code(Left(ActiveCell.Value, 1)))
Tried following syntax, but get runtime error of "method not supported"
MsgBox (Application.WorksheetFunction.code(Left(ActiveCell.Value, 1)))
Any thoughts on porting your answer to code?
So at this point, I am not sure what your actual question is anymore.Code:MsgBox Asc(ActiveCell.Value)
Sorry, Rick. Good point. I was trying to make it simpler. Thanks for patience.
In my first question I thought that the function name in Excel would be same as in VBA. I tried to make the question simpler by asking in the Excel context.
Here is overall goal: Sheet1 col A has a list of countries. Code should go down column A, Copy each country name and paste in sheet 2 in the Col that corresponds to the country's first letter. Australia and Austria would be in col A, Zambia and Zimbabwe in col Z.
I was trying to get the ASCII of the first letter of the country name. From that I could subtract 65 and in the paste use that number as the column argument in Cells(row,col). The use of ASC() in code below returns odd numbers for intColumnTarget (like -12). I've tried adding Application. WorksheetFunction before LEFT but no luck.
Dim intColumnTarget As Integer
Do While Not IsEmpty(ActiveCell)
intColumnTarget = 66 - Asc(Left(ActiveCell.Value, 1))
MsgBox (66 - Asc(Left(ActiveCell.Value, 1)))
ActiveSheet.Paste ' todo: change paste to below last
Does this code do what you are ultimately trying to do?
Notice there is no need to find the numerical equivalent of the column letter as the Cells property can use a string argument for the column reference.Code:Sub MoveCountriesToProperColumn() Dim X As Long, DataLastRow As Long, Col As String Const DataStartRow As Long = 1 DataLastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = DataStartRow To DataLastRow Col = Left(Cells(X, "A").Value, 1) Cells(X, "A").Copy Worksheets("Sheet2").Cells(Rows.Count, Col).End(xlUp).Offset(-(Worksheets("Sheet2").Cells(1, Col).Value <> "")) Next End Sub
Works Perfect, Brilliant.
Learning that CELL() accepts a literal is a big help.
Now I have to go back and parse it in my mind so I understand.
I'd like to send you a few bucks for a beer. Do you have a PayPal acct?
Offer is there, thansk for your effort.
I have parsed in my mind and all makes sense except one part of spec for target to paste:
' begin at paste
' following points to bottom filled cell of correct column to paste
'in other words if doing Angloa and already there are Australia and Austria then line below would point to teh lowest current "A" = Austria
' but then following throws me. I would think from point above the offset would be just (1,0) to go to the empty row below the lowest filled cell. But I can't figure out what this offset is doing, especially the <> at the end.
.Offset(-(Worksheets("Sheet2").Cells(1, Col).Value <> ""))
Any help greatly appreciated. Thanks.
or, more succinctly, like this...
and it would work fine. However, unless you had a header row, the first time it writes to a column, the Offset of one row would make the first entry in that column start on Row 2, not Row 1. So, I test to see if the first cell in the column is empty or not. If it is empty, the comparison would be False, and the offset would be zero rows meaning the first cell in the column would be written to. On the other hand, if the first cell is not blank, then we are writing to a column with data in it and we must offset the row by one. The comparison would be True and, in VB, a True value evaluates as minus one (-1), so I prefix the logical expression with a minus sign so that we have minus a minus one which evaluates as plus one, thus yielding our offset of plus one.