Hi All,

I'd be grateful for some help please. I'm looking for a VBA functio
that will return each letter of the alphabet in turn.

I have a query that brings me back all the people in my list whos
surname begins with the letter "A". I know I could run this query 2
times (to return surnames for each letter) but I'd like to be able t
run it with the result of the function passed as a paramater so that
could end up with a table of all my names. The reason I have to spli
it up 26 times is that the query is so big that it falls over if I as
for all names at once.

Hope that makes sense

TIA

Seamu

--
Message posted from http://www.ExcelForum.com

Hi
have a look at the Chr function in the VBA helpt. Combine this with a
For next loop

--
Regards
Frank Kabel
Frankfurt, Germany


Hi All,

I'd be grateful for some help please. I'm looking for a VBA function
that will return each letter of the alphabet in turn.

I have a query that brings me back all the people in my list whose
surname begins with the letter "A". I know I could run this query 26
times (to return surnames for each letter) but I'd like to be able to
run it with the result of the function passed as a paramater so that
I
could end up with a table of all my names. The reason I have to
split
it up 26 times is that the query is so big that it falls over if I
ask
for all names at once.

Hope that makes sense

TIA

Seamus


---
Message posted from http://www.ExcelForum.com/

Seamus,

Here is a simple function

Dim iLetter As Long

Function NextLetter() As String
If Val(iLetter) = 0 Then
iLetter = 65
Else
iLetter = iLetter + 1
End If
NextLetter = Chr(iLetter)
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"SOS " wrote in message
...
Hi All,

I'd be grateful for some help please. I'm looking for a VBA function
that will return each letter of the alphabet in turn.

I have a query that brings me back all the people in my list whose
surname begins with the letter "A". I know I could run this query 26
times (to return surnames for each letter) but I'd like to be able to
run it with the result of the function passed as a paramater so that I
could end up with a table of all my names. The reason I have to split
it up 26 times is that the query is so big that it falls over if I ask
for all names at once.

Hope that makes sense

TIA

Seamus


---
Message posted from http://www.ExcelForum.com/

Thanks to both Bob and Frank - I will work away with this informatio
and hope that I can incorporate the variable into my SQL statement.

Thanks again

Seamu

--
Message posted from http://www.ExcelForum.com

Seamus,

Try a loop structure like the following:

Dim FirstLetter As String
Dim Ndx As Integer
For Ndx = 65 To 90 ' upper case letters
FirstLetter = Chr(Ndx)
' do you query using FirstLetter
Next Ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"SOS " wrote in message
...
Hi All,

I'd be grateful for some help please. I'm looking for a VBA
function
that will return each letter of the alphabet in turn.

I have a query that brings me back all the people in my list
whose
surname begins with the letter "A". I know I could run this
query 26
times (to return surnames for each letter) but I'd like to be
able to
run it with the result of the function passed as a paramater so
that I
could end up with a table of all my names. The reason I have
to split
it up 26 times is that the query is so big that it falls over
if I ask
for all names at once.

Hope that makes sense

TIA

Seamus


---
Message posted from http://www.ExcelForum.com/

Thanks for the help. Admittedly the work I'm doing is in Access but th
VBA rules apply. I have now got my userform working having adopted th
loop and have my queries running using SQL. I feel quite proud o
myself (IMHO)(LOL)

Thanks again - you guys always come through

Seamu

--
Message posted from http://www.ExcelForum.com