473,322 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

Expand|Select|Wrap|Line Numbers
  1. Public col, nmb, x, y, z As Variant
  2. Public hw(17) As Variant
  3. Public buses(17, 200), sht, dt, nme, per, rnk As String
  4. '
  5. '
  6. '
  7. ' Function Makelist
  8. ' Updated by DEE 5/10/1999
  9. '
  10. Sub Makelist()
  11.     sht = ActiveSheet.Name
  12.     col = 1
  13.     msg = "Would you like to print individual Regions for " + Format(sht, "mmmm yyyy") + "?"
  14.     Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
  15.     Title = "What is your bidding, master?"
  16.     response = MsgBox(msg, Style, Title)
  17.     If response = vbCancel Then End
  18.     If response = vbYes Then
  19.         dist = InputBox(Title:="Single Region Printing", prompt:="Which Region?", default:="1")
  20.     If dist = "" Or Val(dist) > 4 Then End
  21.         z = Val(dist)
  22.         all = False
  23.     Else
  24.         all = True
  25.     End If
  26.  
  27.     whosename
  28.  
  29.     Sheets(Format(sht, "mmmm yyyy")).Select
  30.     If Not all Then
  31.         z = Val(dist)
  32.         Gather
  33.     ElseIf all Then
  34.         For z = 1 To 4
  35.             Gather
  36.         Next z
  37.     End If
  38. '
  39. '   Clear Details
Dec 17 '09 #1
8 1725
!NoItAll
297 100+
Expand|Select|Wrap|Line Numbers
  1. Dim NumberLetters(4) as String = {"", "N", "W", "E", "S"}
  2. Region = InputBox()
  3. Return NumberLetters(Cint(Region))
You'll need to do some error checking of course...
Dec 17 '09 #2
tlhintoq
3,525 Expert 2GB
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.
Dec 17 '09 #3
tlhintoq
3,525 Expert 2GB
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
Dec 17 '09 #4
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

Expand|Select|Wrap|Line Numbers
  1. Public col, nmb, x, y, z As Variant
  2. Public hw(17) As Variant
  3. Public buses(17, 200), sht, dt, nme, per, rnk As String
  4. '
  5. '
  6. '
  7. ' Function Makelist
  8. ' Updated by DEE 5/10/1999
  9. '
  10. Sub Makelist()
  11.     sht = ActiveSheet.Name
  12.     col = 1
  13.     msg = "Would you like to print individual Regions for " + Format(sht, "mmmm yyyy") + "?"
  14.     Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
  15.     Title = "What is your bidding, master?"
  16.     response = MsgBox(msg, Style, Title)
  17.     If response = vbCancel Then End
  18.     If response = vbYes Then
  19.         dist = InputBox(Title:="Single Region Printing", prompt:="Which Region?", default:="1")
  20.     If dist = "" Or Val(dist) > 4 Then End
  21.         z = Val(dist)
  22.         all = False
  23.     Else
  24.         all = True
  25.     End If
  26.  
  27.     whosename
  28.  
  29.     Sheets(Format(sht, "mmmm yyyy")).Select
  30.     If Not all Then
  31.         z = Val(dist)
  32.         Gather
  33.     ElseIf all Then
  34.         For z = 1 To 4
  35.             Gather
  36.         Next z
  37.     End If
Dec 17 '09 #5
I'm happy to forward the whole file if someone can point me in the right direction.
Dec 17 '09 #6
tlhintoq
3,525 Expert 2GB
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?
Dec 17 '09 #7
tlhintoq
3,525 Expert 2GB
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?
Dec 17 '09 #8
tlhintoq
3,525 Expert 2GB
How / where would I insert the response from !NoItAll?
!NoItAll is suggesting that you create an array of letters that corresponds to your numbering
Expand|Select|Wrap|Line Numbers
  1. 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"
Dec 17 '09 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: PhilC | last post by:
Hi Folks, If I have an array holding a pair of numbers, and that pairing is unique, is there a way that I can find the array index number for that pair? Thanks, PhilC
1
by: taras | last post by:
IE 6.0 sockets number (TCP/IP channels number) for the same Site ??? I've discovered the restriction: IE 6.0 has 2 sockets only to the same Server (ASP.NET Site). Where can I find any kind of...
2
by: mikeybe | last post by:
Anyone know how to return the disk number (i.e. the number you see when you launch windows disk manager and see your drives numbered disk 0, disk 1, etc.)? I'm not talking about the volume serial...
12
by: tojigneshshah | last post by:
Hi, I have a situation where i have multiple batch and the each batch are sequence numbers. For each batch, the number should start with 1. For example: Col.no1 Col.no2 ------ ...
3
by: petermichaux | last post by:
Hi, Is there an internally assigned number for each JavaScript object that I can read? If so how would I do that? Thanks, Peter
2
by: rhepsi | last post by:
Hii all, I have a project where before i upload the data from 1st database to 2nd database, im truncating the 2nd database and then trying to upload... In this process, wat i found was that:...
1
by: Visame | last post by:
ofstream out1, out2; out1 = out2; // error: cannot assign stream objects // print function: parameter is copied ofstream print(ofstream); out2 = print(out2); // error: cannot...
2
by: sheperson | last post by:
Hi, I have been working for several months on a sofware and now it is finished. I have a problem now and it is this: Because I live in a country which there is no copyright law in it (Iran!!!!)....
8
chandru8
by: chandru8 | last post by:
hi every body i need a logic , that for a particular code i need to genrate a serial number for a day should be one for the next day it should be increment to 2 like this can any one help me...
8
by: davidson1 | last post by:
Hai friends.... In C for 97=A character , 65=a charcter....can u tell what is the number for "space" character... Davidson
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.