Assign letter to a number? where "for z = 1 - 4" I want 1 =N 2=W 3=E 4=S

[Twoshots64]

Public col, nmb, x, y, z As Variant
Public hw(17) As Variant
Public buses(17, 200), sht, dt, nme, per, rnk As String
'
'
'
' Function Makelist
' Updated by DEE 5/10/1999
'
Sub Makelist()
    sht = ActiveSheet.Name
    col = 1
    msg = "Would you like to print individual Regions for " + Format(sht, "mmmm yyyy") + "?"
    Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
    Title = "What is your bidding, master?"
    response = MsgBox(msg, Style, Title)
    If response = vbCancel Then End
    If response = vbYes Then
        dist = InputBox(Title:="Single Region Printing", prompt:="Which Region?", default:="1")
    If dist = "" Or Val(dist) > 4 Then End
        z = Val(dist)
        all = False
    Else
        all = True
    End If
 
    whosename
 
    Sheets(Format(sht, "mmmm yyyy")).Select
    If Not all Then
        z = Val(dist)
        Gather
    ElseIf all Then
        For z = 1 To 4
            Gather
        Next z
    End If
'
'   Clear Details

[!NoItAll]

Dim NumberLetters(4) as String = {"", "N", "W", "E", "S"}
Region = InputBox()
Return NumberLetters(Cint(Region))

You'll need to do some error checking of course...

[tlhintoq]

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

[tlhintoq]

Mate, it would really help if you could describe your need/question.
The little bit that fits in the title doesn't give us the big picture of what you are trying to accomplish

[Twoshots64]

My apologies, but I am not literate in VBA code etc. I have "inherited" an Excel program written some years ago that designs a "roster" and gather information from another spreadsheet and then produces a report from that roster.

Now they have changed the way the "label" the locations from, for example
1MW to NMW or 2AN to WAN. So now I need to understand how to change the numerical reference to an alpha reference and still produce the same reports, hopefully without resorting having to have someone re-write the whole thing.

How / where would I insert the response from !NoItAll?

Into this.

Mick's Stuff
' Macro Updated on 5/10/1999 by DEE
'
'
'
'
'Declarations

Public col, nmb, x, y, z As Variant
Public hw(17) As Variant
Public buses(17, 200), sht, dt, nme, per, rnk As String
'
'
'
' Function Makelist
' Updated by DEE 5/10/1999
'
Sub Makelist()
    sht = ActiveSheet.Name
    col = 1
    msg = "Would you like to print individual Regions for " + Format(sht, "mmmm yyyy") + "?"
    Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
    Title = "What is your bidding, master?"
    response = MsgBox(msg, Style, Title)
    If response = vbCancel Then End
    If response = vbYes Then
        dist = InputBox(Title:="Single Region Printing", prompt:="Which Region?", default:="1")
    If dist = "" Or Val(dist) > 4 Then End
        z = Val(dist)
        all = False
    Else
        all = True
    End If
 
    whosename
 
    Sheets(Format(sht, "mmmm yyyy")).Select
    If Not all Then
        z = Val(dist)
        Gather
    ElseIf all Then
        For z = 1 To 4
            Gather
        Next z
    End If

[Twoshots64]

I'm happy to forward the whole file if someone can point me in the right direction.

[tlhintoq]

1MW to NMW or 2AN to WAN.

So the codes are already 1MW, 2AN etc.?
So you aren't just inserting a letter, but you need to actually read the existing code and change it based on the first letter of the code.
Is that right?

[tlhintoq]

1MW to NMW or 2AN to WAN.

So the codes are already 1MW, 2AN etc.?
So you aren't just inserting a letter, but you need to actually read the existing code and change it based on the first letter of the code.
Is that right?

[tlhintoq]

How / where would I insert the response from !NoItAll?

!NoItAll is suggesting that you create an array of letters that corresponds to your numbering

Dim NumberLetters(4) as String = {"", "N", "W", "E", "S"}

Element 1 is "N"
Element 2 is "W" and so on

This way you can match (or rebuild) your code "2an" to "Wan" by just taking the first character of the code, convert it to number and grab that element of the array.

"2" as a string becomes the int of 2.
NumberLetters[2] is "W"