Convert a character to its ASCII value

[John Kauffman]

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"

another attempt:
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.

 

[Rick Rothstein]

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"

ASC is the VB function that returns the ASCII code... in an Excel worksheet formula, the function name is CODE, so try this...

=CODE(A1)

 

[John Kauffman]

Thanks, Rick. Ah, I'm revealing my background. I tried CODE("char") in VBA but threw error as below

precursor works:
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?
Thanks.

 

[Rick Rothstein]

Thanks, Rick. Ah, I'm revealing my background. I tried CODE("char") in VBA but threw error as below

precursor works:
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?
Thanks.

I'm confused. Your original message said this...

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"

which meant to me you were looking for an Excel formula solution which would be the =CODE(A1) formula that I posted. But your latest message is talking about VBA code which does not use the CODE function (as that is strictly a worksheet function) but, rather, use the Asc function that you talked about in your original message....

MsgBox Asc(ActiveCell.Value)

So at this point, I am not sure what your actual question is anymore.

 

[John Kauffman]

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.

Sub ToDo_MoveAndSortToOtherSheet()
Dim intColumnTarget As Integer
Do While Not IsEmpty(ActiveCell)
intColumnTarget = 66 - Asc(Left(ActiveCell.Value, 1))
MsgBox (66 - Asc(Left(ActiveCell.Value, 1)))
Selection.Copy
Sheets("Countries-Sorted").Select
Cells(1, intColumnTarget).Select
ActiveSheet.Paste ' todo: change paste to below last
Sheets("COuntries-Random").Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Rick Rothstein]

Does this code do what you are ultimately trying to do?

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

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.

 

[John Kauffman]

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?

 

[Rick Rothstein]

I'd like to send you a few bucks for a beer. Do you have a PayPal acct?

I'm glad the code worked for you and if you have any follow up questions, simply post them to this thread and I will (eventually) see them. As for sending me money... do not do it... volunteering to answer newsgroup and forum question is a hobby for me, I do not do it for money. Besides, I hate beer.

 

[John Kauffman]

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
Worksheets("Sheet2").Cells(Rows.Count, Col).End(xlUp)

' 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.

 

[Rick Rothstein]

' 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 <> ""))

Yes, you could write the above like this...

.Offset(1, 0)

or, more succinctly, like this...

.Offset(1)

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.