Convert numerics to alpabetics?

[peter]

I need to fill 19 adjacent cells with the letters B to S
and want to do it programmatically.

I suspect I need a function to generate a String from a Byte.

I've found "Type Conversion Functions",
but it doesn't help much.

Thanks for your help,
Peter.

 

[Chip]

Try
=CHAR(66)

to CHAR(83)

Just create a loop with the counter increasing. Let me know if you
need help.

 

[Bob Phillips]

Put this formula in the first

=CHAR(COLUMN(B1)+64)

and then copy across

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[peter]

Thanks a lot guys, but I've still got some questions.

1. If I'm doing it as an Excel /VBA macro,
why doesn't this work:
s = Application.WorksheetFunction.CHAR(65)
It says, at runtime, the property/method isn't supported.

2. I still can't find this in my books, even the Dummies one.
Why? I can, just, understand why it's not in the indexes
of the non-Microsoft books, but it's not in that one either.
They are all good books too: WROX, O'Reilly and 2 x microsoft.

Thanks again,
Peter.

 

[Tim Williams]

Not all WS functions are available in VBA, particularly those that have a
similar VBA function (CHAR() vs Chr())

To see all those which are available, open the Excel VBE and open the object
browser (F2). They are listed under Application>>Worksheetfunction

tim.

 

[Ron Rosenfeld]

I need to fill 19 adjacent cells with the letters B to S
and want to do it programmatically.

I suspect I need a function to generate a String from a Byte.

Well, here's one way to do it via VBA:

==================
Sub foo()
Const FirstCol As Long = 3 'Start in Column C
Const Rw As Long = 2 ' Row 2
Dim i As Long, j As Long

j = 66 'Code for B
For i = FirstCol To FirstCol + 17
Cells(Rw, i) = Chr(j)
j = j + 1
Next i

End Sub
=====================


--ron

 

[Peter T]

Hi Peter,

1. If I'm doing it as an Excel /VBA macro,
why doesn't this work:
s = Application.WorksheetFunction.CHAR(65)
It says, at runtime, the property/method isn't supported.

In VBA simply: s =chr(65)

2. I still can't find this in my books, even the Dummies one.
Why? I can, just, understand why it's not in the indexes
of the non-Microsoft books, but it's not in that one either.
They are all good books too: WROX, O'Reilly and 2 x microsoft.

Not all worksheet functions are supported in VBA. See WorksheetFunction in
help for a complete list of those which are.

Why not create a custom list A to Z and use Autofill whenever wou want,
manually or programatically. Just a thought.

Sub AZlist()
Dim v(25)
For i = 0 To 25
v(i) = Chr(65 + i)
Next
Application.AddCustomList v

'' test list
'[b1] = "B"
'[b1].AutoFill [B1:T1]
'[b1].AutoFill [b1:b19]

End Sub

Regards,
Peter T

 

[Dana DeLouis]

why doesn't this work:

s = Application.WorksheetFunction.CHAR(65)

Vba has it's own function for this, so the CHAR function was not included.

s = Chr(65)

I need to fill 19 adjacent cells with the letters B to S
and want to do it programmatically.

Just a thought. Weeks down the road, reading the code might be a little
hard to follow. One idea might be to use:
(s & e are Start & End)

The reason I mention this is that you will see that "B" is 66, and not 65.
s = Asc("B")
e = Asc("S")

Also, if you run this code, you will see that there are 18 letters, and not
19.

Debug.Print "Number of Letters: " & e - s + 1

Number of Letters: 18

Again, just some ideas. It can get confusing, so I hope I said this right.
:>)
--
Dana DeLouis
Win XP & Office 2003

I need to fill 19 adjacent cells with the letters B to S
and want to do it programmatically.