Define name for a dynamic list

[joanna_gr]

A big hello to all experts in here! :bow: this is another one question that came up today. I have a dynamic list of names and I want to define a different name depending the first letter of the text in a cell. An example: If cells A1:A20 start with A then define>name : "Alpha". I found the following macro which works fine as fas as I have only one column. But I need to include more columns in each range. So, as usual :help please...


Sub Macro2()
Dim StartRow As Long, EndRow As Long, LastRow As Long, iROW As Long
Dim AscFirstChar As Integer
Dim Str4Name As String
Str4Name = "StartsWith_"
StartRow = 1
LastRow = Range("B65536").End(xlUp).Row
AscFirstChar = 65 'ASCII code for "A"
Cells(LastRow + 1, 2) = "1" 'Necessary to avoid error
Do While StartRow <= LastRow And AscFirstChar <= 90
If Asc(UCase(Left(Cells(StartRow, 2), 1))) = AscFirstChar Then
EndRow = StartRow
Do While Asc(UCase(Left(Cells(EndRow + 1, 2), 1))) = AscFirstChar
EndRow = EndRow + 1
Loop
ActiveWorkbook.Names.Add Name:=Str4Name & Chr(AscFirstChar), RefersToR1C1:= _
"=" & ActiveSheet.Name & "!" & Range(Cells(StartRow, 2), Cells(EndRow, 2)).Address(True, True, xlR1C1)
StartRow = EndRow + 1
AscFirstChar = AscFirstChar + 1
Else
AscFirstChar = AscFirstChar + 1
End If
Loop
Cells(LastRow + 1, 2).ClearContents
End Sub

[Paleo]

Hi Joanna,

try this:



Sub Macro2()
Dim StartRow As Long, EndRow As Long, LastRow As Long, iROW As Long
Dim AscFirstChar As Integer
Dim Str4Name As String
Str4Name = "StartsWith_"
StartRow = 1
LastRow = Range("B65536").End(xlUp).Row
AscFirstChar = 65 'ASCII code for "A"
Cells(LastRow + 1, Range("IV1").End(xlToLeft).Cells.Column) = "1" 'Necessary to avoid error
Do While StartRow <= LastRow And AscFirstChar <= 90
If Asc(UCase(Left(Cells(StartRow, 2), 1))) = AscFirstChar Then
EndRow = StartRow
Do While Asc(UCase(Left(Cells(EndRow + 1, 2), 1))) = AscFirstChar
EndRow = EndRow + 1
Loop
ActiveWorkbook.Names.Add Name:=Str4Name & Chr(AscFirstChar), RefersToR1C1:= _
"=" & ActiveSheet.Name & "!" & Range(Cells(StartRow, 2), Cells(EndRow, Range("IV" & StartRow).End(xlToLeft).Cells.Column)).Address(True, True, xlR1C1)
StartRow = EndRow + 1
AscFirstChar = AscFirstChar + 1
Else
AscFirstChar = AscFirstChar + 1
End If
Loop
Cells(LastRow + 1, 2).ClearContents
End Sub

[joanna_gr]

there is a syntax error in:

ActiveWorkbook.Names.Add Name:=Str4Name & Chr(AscFirstChar), RefersToR1C1:= _
"=" & ActiveSheet.Name & "!" & Range(Cells(StartRow, 2), Cells(EndRow, Range("IV" & StartRow).End(xlToLeft).Cells.Column)).Address(Tru e, True, xlR1C1)
:(

[Paleo]

there is a syntax error in:
ActiveWorkbook.Names.Add Name:=Str4Name & Chr(AscFirstChar), RefersToR1C1:= _
"=" & ActiveSheet.Name & "!" & Range(Cells(StartRow, 2), Cells(EndRow, Range("IV" & StartRow).End(xlToLeft).Cells.Column)).Address(Tru e, True, xlR1C1)
:(


Hi Joanna,

somehow a space got in the code, just delete them. This line must be like this:

ActiveWorkbook.Names.Add Name:=Str4Name & Chr(AscFirstChar), RefersToR1C1:= _
"=" & ActiveSheet.Name & "!" & Range(Cells(StartRow, 2), Cells(EndRow, Range("IV" & StartRow).End(xlToLeft).Cells.Column)).Address(True, True, xlR1C1)

[joanna_gr]

ehmmm, now there is a debug in line:


If Asc(UCase(Left(Cells(StartRow, 2), 1))) = AscFirstChar Then

:dunno :(

[Paleo]

Which error are you getting?

[joanna_gr]

Run time error '5'
Invalid procedure call or argument

[Paleo]

You must have something other than this code running because its running fine to me. Can you post your whole macro here?

[joanna_gr]

well I think I got it. For some reason it only recognises english characters and runs great as far as the list is in english. When in Greek there is no result. Any help on this?:dunno :help please...

[shades]

I wonder if that has to do with the ASCII designation difference between Greek and English?

[joanna_gr]

probably. but if so what can we do about? :dunno

[sandam]

ascii is an american standard that was integrated into the computer world many years ago. It only defines a specific set of characters with values 0 - 255 (its meant to work with bytes). You can look at the other char set (128-255) and see if there's anything there that may help but otherwise you can try referencing your own spreadsheet of greek and english characters (you woudl have to set this up yourself) or create a module in your personal.xls with constants defining the character values of greek chars and work with that??

HTH
Andrew;?

[joanna_gr]

I used a formula to do my work (offset, index etc). It works fine but it takes much more time to create a range for each character and need more attention.

Just a thought: If a formula can see the greek and english why a code can't ?


Sorry sandam but I'm not sure I understand... How can I do all these??? :think:

[sandam]

i can't create a sample here at work but i'll post something when i get home tonight :)

[joanna_gr]

Oh yes! I did it! A miracle! :rotlaugh: :rotlaugh: It worked when I replaced "65" to "128" and deleted " And AscFirstChar <= 90 ". It seems that my language is "greek" for coding ... lol.

I was sure that nothing is impossible with VBA. The only thing that cannot be done by excel is coffee... lol... or maybe you experts have found a way to solve this also... ;).
Thank you very much. You are so great! :bow: :clap:


Sandam > If i'm not asking much and if you have time please send me this sample xl you said.: pray2: Just to expand my knowledge.

[sandam]

No problemo, I recall reading that the ascii chars in 128 - 255 are determined by your operating system's settings. As for the sample, 'fraid i didn't get anywhere with it last night but I'll continue on tonight :)