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.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Try
=CHAR(66)

to CHAR(83)

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

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" wrote in message
...
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.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 ApplicationWorksheetfunction

tim.


"peter" wrote in message
...

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.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Wed, 23 Feb 2005 13:59:16 -0800, peter wrote:

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

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

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


"peter" wrote in message
...
I need to fill 19 adjacent cells with the letters B to S
and want to do it programmatically.


--
Dana DeLouis
Win XP & Office 2003


"peter" wrote in message
...

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.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!