Coverting from 3 letters to 1

I currently have a program that can convert codes from 1 letter to 3 an
a number that is involved with the letters. This is the macro. 

MODULE 
Option Explicit
Function myConversion(rng As Range) As String

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myStr As String

Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:b")

myStr = ""
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 2, False)
If IsError(res) Then
myStr = myStr & "-?"
Else
myStr = myStr & "-" & res
End If
Next iCtr

If myStr <> "" Then
myStr = Mid(myStr, 2)
End If

myConversion = myStr

End Function

MODULE 
Option Explicit
Function myConversionA(rng As Range) As Double
'returns a whole number???
' As Double
'if you have fractions

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myValue As Double

Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:c")

myValue = 0
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 3, False)

If IsError(res) Then
'do nothing
Else
If IsNumeric(res) Then
myValue = myValue + res
End If
End If

Next iCtr

myConversionA = myValue

End Function

I've been playing around with the macro trying to get it to covert fro
3 to 1 instead of from 1 to 3 but every change I make is ruining th
macro. Thanks in advance

--
Pookie7
-----------------------------------------------------------------------
Pookie76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1281
View this thread: http://www.excelforum.com/showthread.php?threadid=46901

0
9/19/2005 10:18:15 PM
excel 39879 articles. 2 followers. Follow

5 Replies
445 Views

Similar Articles

[PageSpeed] 21

If you don't get a helpful reply, you may want to describe what you're trying to
do.

Maybe some examples of what you start with and what you should end with.

And what function you're using--you posted two functions.



Pookie76 wrote:
> 
> I currently have a program that can convert codes from 1 letter to 3 and
> a number that is involved with the letters. This is the macro.
> 
> MODULE 1
> Option Explicit
> Function myConversion(rng As Range) As String
> 
> Dim res As Variant
> Dim LookUpTable As Range
> Dim iCtr As Long
> Dim myStr As String
> 
> Set rng = rng(1)
> Set LookUpTable = Worksheets("sheet2").Range("a:b")
> 
> myStr = ""
> For iCtr = 1 To Len(rng.Value)
> res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
> LookUpTable, 2, False)
> If IsError(res) Then
> myStr = myStr & "-?"
> Else
> myStr = myStr & "-" & res
> End If
> Next iCtr
> 
> If myStr <> "" Then
> myStr = Mid(myStr, 2)
> End If
> 
> myConversion = myStr
> 
> End Function
> 
> MODULE 2
> Option Explicit
> Function myConversionA(rng As Range) As Double
> 'returns a whole number???
> ' As Double
> 'if you have fractions
> 
> Dim res As Variant
> Dim LookUpTable As Range
> Dim iCtr As Long
> Dim myValue As Double
> 
> Set rng = rng(1)
> Set LookUpTable = Worksheets("sheet2").Range("a:c")
> 
> myValue = 0
> For iCtr = 1 To Len(rng.Value)
> res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
> LookUpTable, 3, False)
> 
> If IsError(res) Then
> 'do nothing
> Else
> If IsNumeric(res) Then
> myValue = myValue + res
> End If
> End If
> 
> Next iCtr
> 
> myConversionA = myValue
> 
> End Function
> 
> I've been playing around with the macro trying to get it to covert from
> 3 to 1 instead of from 1 to 3 but every change I make is ruining the
> macro. Thanks in advance.
> 
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12815
> View this thread: http://www.excelforum.com/showthread.php?threadid=469014

-- 

Dave Peterson
0
petersod (12004)
9/20/2005 12:36:47 AM
Dave Peterson Wrote:
> If you don't get a helpful reply, you may want to describe what you'r
> trying to
> do.
> 
> Maybe some examples of what you start with and what you should en
> with.
> 
> And what function you're using--you posted two functions.
> 
> 

The first function coverts a 1 letter code to a 3 letter code. Th
second function coverts the 3 letter code into a sum of numerica
values. 
Ie. 
If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in 
sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value become
6. 

Thanks

--
Pookie7
-----------------------------------------------------------------------
Pookie76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1281
View this thread: http://www.excelforum.com/showthread.php?threadid=46901

0
9/20/2005 2:13:55 AM
Do you base that sum on the original value (ABC) or do you have a table that
shows the numeric equivalent for ABA, BCA, CAB, etc?



Pookie76 wrote:
> 
> Dave Peterson Wrote:
> > If you don't get a helpful reply, you may want to describe what you're
> > trying to
> > do.
> >
> > Maybe some examples of what you start with and what you should end
> > with.
> >
> > And what function you're using--you posted two functions.
> >
> >
> 
> The first function coverts a 1 letter code to a 3 letter code. The
> second function coverts the 3 letter code into a sum of numerical
> values.
> Ie.
> If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in a
> sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value becomes
> 6.
> 
> Thanks.
> 
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12815
> View this thread: http://www.excelforum.com/showthread.php?threadid=469014

-- 

Dave Peterson
0
petersod (12004)
9/20/2005 3:22:35 AM
Sheet 2 has 3 rows. 
column A is for the 1 letter code, column b is the 3 letter code and
column c in the value. 

Dave Peterson Wrote: 
> Do you base that sum on the original value (ABC) or do you have a table
> that
> shows the numeric equivalent for ABA, BCA, CAB, etc?
> 
> 
> 
> Pookie76 wrote:
> >
> > Dave Peterson Wrote:
> > > If you don't get a helpful reply, you may want to describe what
> you're
> > > trying to
> > > do.
> > >
> > > Maybe some examples of what you start with and what you should end
> > > with.
> > >
> > > And what function you're using--you posted two functions.
> > >
> > >
> >
> > The first function coverts a 1 letter code to a 3 letter code. The
> > second function coverts the 3 letter code into a sum of numerical
> > values.
> > Ie.
> > If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in
> a
> > sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value
> becomes
> > 6.
> >
> > Thanks.
> >
> > --
> > Pookie76
> >
> ------------------------------------------------------------------------
> > Pookie76's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=12815
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=469014
> 
> --
> 
> Dave Peterson


-- 
Pookie76
------------------------------------------------------------------------
Pookie76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12815
View this thread: http://www.excelforum.com/showthread.php?threadid=469014

0
9/21/2005 3:56:00 AM
Then maybe it's as simple as looking at columns B:C.

MODULE 2
Option Explicit
Function myConversionA(rng As Range) As Double
'returns a whole number???
' As Double
'if you have fractions

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myValue As Double

Set rng = rng(1)
'next line changed
Set LookUpTable = Worksheets("sheet2").Range("b:c")  

myValue = 0
'next few lines changed
For iCtr = 1 To Len(rng.Value) step 3   
res = Application.VLookup(Mid(rng.Value, iCtr, 3), LookUpTable, 2, False)

If IsError(res) Then
'do nothing
Else
If IsNumeric(res) Then
myValue = myValue + res
End If
End If

Next iCtr

myConversionA = myValue

End Function

Pookie76 wrote:
> 
> Sheet 2 has 3 rows.
> column A is for the 1 letter code, column b is the 3 letter code and
> column c in the value.
> 
> Dave Peterson Wrote:
> > Do you base that sum on the original value (ABC) or do you have a table
> > that
> > shows the numeric equivalent for ABA, BCA, CAB, etc?
> >
> >
> >
> > Pookie76 wrote:
> > >
> > > Dave Peterson Wrote:
> > > > If you don't get a helpful reply, you may want to describe what
> > you're
> > > > trying to
> > > > do.
> > > >
> > > > Maybe some examples of what you start with and what you should end
> > > > with.
> > > >
> > > > And what function you're using--you posted two functions.
> > > >
> > > >
> > >
> > > The first function coverts a 1 letter code to a 3 letter code. The
> > > second function coverts the 3 letter code into a sum of numerical
> > > values.
> > > Ie.
> > > If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in
> > a
> > > sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value
> > becomes
> > > 6.
> > >
> > > Thanks.
> > >
> > > --
> > > Pookie76
> > >
> > ------------------------------------------------------------------------
> > > Pookie76's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=12815
> > > View this thread:
> > http://www.excelforum.com/showthread.php?threadid=469014
> >
> > --
> >
> > Dave Peterson
> 
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12815
> View this thread: http://www.excelforum.com/showthread.php?threadid=469014

-- 

Dave Peterson
0
petersod (12004)
9/21/2005 11:38:33 AM
Reply:

Similar Artilces:

coverting pub files to jpeg
How do i convert a multipage publisher file to jpeg so i can send to someone via email who does not have publisher? -- I am new to windows xp and am looking for help and guidance. I appreciate your time. Thanks people Convert it to pdf. You can get a free or inexpensive pdf converting program at: Primopdf at www.Primopdf.com it's free or a low cost program, PDF-XChange at; http://www.docu-track.com/ -- Don Vancouver USA "kezaaa" <kezaaa@discussions.microsoft.com> wrote in message news:E8546816-9E55-4BB3-B03F-1D42555F98F6@microsoft.com... > How do i conver...

CRM 3.0 + Domain Rename
I am currently preparing for a domain rename in my AD environment. I am planing on using the rendom utilities as well as the exchange fixup for this project. I am not able to find any information however on whether or not CRM will work under this scenario. All that I have been able to find is that CRM is linked to the GUID's in AD which do not change under a domain renaming scenario (only the DNS name and NetBIOS name are chaning, GUID's do not). Has anyone tried this and did you have any success? I just need to know if this is possible or if I will need to use the redeploy...

how to covert a jpeg to a word document?
I scanned articleand saved as a jpeg i want to put the text in a word document also use the pictures seperately any ideas how? Sounds like you need OCR (optical character recognition) software and rescan it creating a Word or other word processor document. -- Don Vancouver, USA "aisha stacey" <aisha stacey@discussions.microsoft.com> wrote in message news:79D8C2CE-6691-48CA-AD4C-833F68B47333@microsoft.com... > I scanned articleand saved as a jpeg i want to put the text in a word > document also use the pictures seperately any ideas how? basically you can't....

Money won't start #3
When I double-click Money, it opens initially, but then after a couple of seconds, it closes. This is before I see any logon or even any splash screen. Money used to work on this computer, a few weeks ago, but now it doesn't. I have installed several programs on the computer, most recently Google Earth, but I don't know exactly when the program stopped working. However, I believe it stopped working when I detached the external hard drive where I had my primary Money file. I copied the Money file to the internal hard drive on my laptop, but even if I double click that, Money ...

Run ALL rules at once w/o checking 1 by 1?
OLE6 Is there a way to manually run all rules at once without checking each one, one by one? Thanks, Ken Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my pe...

Rental Addon #3
We are pleased to announce release of RentMe addon for RMS 2.0 and 1.3 You can view nine minute video here. http://www.retail-pos.com/2008-06-10%2020.38%20Meet%20Now.wmv Please contact us for detailed information. Ian McLean RMS sales(AT)retail-pos.com ...

Recovering Contact list for 1 mailbox?
I was testing the sync feature of 10.3 and tested a sync with exchange. It worked, I tried to set my boss up and it wasn't working. So I did some more tests with different accounts. None worked and so I went changed it back to mine and it worked. Worked so well that it sych'ed the empty address TO my exchange account. Now all my contacts are freaking gone. Anyway, is there anyway to recover the contact list? I am backing up Exchange via W2k3 backup. do you have DIR configured? if so, see this...it might work in this scenario... http://support.microsoft.com/default.aspx?sc...

Comment boxes #3
As far as I can tell, this cannot be done, but I figured it couldn't hurt to ask...Can a comment box be "assigned" to more than one cell? Thanks. -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winne the Pooh I didn't follow your question. Can you provide an example ----- Michael Malinsky wrote: ---- As far as I can tell, this cannot be done, but I figured it couldn't hurt t ask...Can a comment box be "assigned" to more than one cell Thanks ...

Changing 1 word to another word on a different page
I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", I need it to display the opposite in a cell on page 1 In a1 in sheet1 put =IF(Sheet2!A1="yes","no","") -- Russell Dawson Excel student "Circuitman57" wrote: > I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", > I need it to display the opposite in a cell on page 1 Missed a bit =IF(Sheet2!A1="yes","no",IF(Sheet2!A1="no","yes","")) -- ...

Help!! Where are my Page Breaks for Page 2,3 etc.
I use Excell for XP and want to add Pagebreaks. Only Page 1 is visible after Menu > View > Pagebraks. I canot add braks with selecting row/column and rightclick. No result after both setting up Print Area and reset Print Area. -- Thank you for your help. Bart Remove X from my e-mail address to send me an e-mail Quite la X de mi direcci�n para enviarme correo electr�nico ____________________ 1.. Bart, does this help? On the View menu, click Page Break Preview. Do one of the following: View page breaks Manually inserted page breaks appear as solid lines. Dashed lines indicat...

No maintenance done for 1 1/2 years
No maintenance was done in GP for 1 1/2 years and we would like to do so... What are the steps and how to do it...we use GP 9.0 Can you tell Newbie~~~ What kind of maintenance do you want to do? -- Charles Allen, MVP "Newbie" wrote: > No maintenance was done in GP for 1 1/2 years and we would like to do so... > What are the steps and how to do it...we use GP 9.0 > Can you tell Newbie~~~ What is happening that would lead you to believe that "maintenance" is required? -- Lyle U Newbie wrote: > No maintenance was done in GP for 1 1/2 years and we woul...

Chart the Top 3 values
I'm a programmer, but I don't program in Excel normally so bear with me. I currently have an Excel chart that displays the frequency of Accidents in Man Hours Lost for the overall Program with the x-axis being each Qtr. Within this Program are 40 Projects. What I would like to do is chart the top 3 most accident prone Projects as an overlay on the existing chart. I've worked out how to add a combo box that will add a series to the chart based on the user's selection(s), which gives them the ability to add desired project ranges to the chart. But, I would like to make ...

Coverting to 2007
How do I convert Access 2002 versions 2007 versions in Access 2007. I just upgraded to 2007 and all of the quesries, tables etc are still in 2002 version. Unless you need something that is unique to 2007, such as attachment fields, I recommend just leaving it in the 2002-2003 file format. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Gringarlow" wrote: > How do I convert Access 2002 versions 2007 versions in Access 2007. I just > upgraded to 2007 and all of the quesries, tables etc are still in ...

CRectTracker #3
Is there any way to invoke a CRectTracker object so that it allows re-size, but not translation? If so, could someone elucidate for me, please? Thanks in advance. JcP ...

Surface Chart #3
When plotting a 3D surface chart from a data field size of 35 cells by 62 cells, why does my chart only display half of the data? The data is symmetrical, left side is identical to the right side, but I need the chart to display the lot. Any ideas? Sorry guys, sorted it out now. I had to plot the graph using a very small selection of cells then expand the range after the chart was created to include all I needed. If I created the chart with the full range it split the data selection in two. ...

Update from CRM 3.0 to new CRM 4.0
At the moment we are runnig CRM 3.0. Because of the new multi-language feature of CRM 4.0 we want tu upgrate/update to the new version. Does anybody has further information about the necessary steps? I suggest that you wait for the officially released version which will contain upgrade and installation guides. -- ----------------- Please note that the contents of my posts are my personal opinions and views and I am not a Microsoft staff member. ----------------- Feridun Kadir MCSE, MCT "Frank_Munich" wrote: > At the moment we are runnig CRM 3.0. Because of the new multi-...

formula for updating 1 spreadhseet from another
I have two spreadsheets one for "current accounts" and one for "past due accounts". The "current" spreadsheet list all our receivables from this year even if they are still outstanding, the "past due" spreadsheet list only those that are 30 days or more past due. What I currently have to do is if a vendor pays an invoice update the "current" spreadsheet and then if its a past due go an update the "past due spreadhseet". My question: Is there away to automaticly have the "past due" spreadhseet update itself if I enter a payme...

Moving CRM 1.x databases to new server
Hi all Can anyone point me to some good information on how to move CRM databases from one SQL 2000 server to another? I've been asked to do a feasibility assessment. Thanks Tony Afraid with 1.x it is not that easy. First, refer to the Implementation Guide and look at the Disaster Recovery section. It has the basic steps for "recovering" SQL to a new server. This is the same basic approach you need to follow. Make sure everything is working before droping the old DB's though as you may need to go back. Also, make sure you have good backups f everything (including yo...

Coverting All incoming email question...
We are using CRM 1.2. When I am logged in as a user and I go to tools, options, and then select the activities tab, there is an option to "convert in-coming email". There are two options. By default, we have the "only e-mail about existing Microsoft CRM records" selected. My question is that if we select the other option "all incoming e-mail", and we change the setting on the server to allow this, can the user toggle back to the other selection. I just want to make sure that it can be toggled by the user. I am afraid that if I allow this I will alway...

default From field #3
Is there any way to set a default value for "from" field in new messages (the from account may be different from the account that log on the server)? thanks ...

CRM 3.0 for current customer...when do I get this?
I have an active service agreement, but still haven't received CRM 3.0, even though I've read here that it shipped December 6. Also, it's not available for download from Customer Source and we're still listed as being licensed for 1.2 only. Can anyone please explain the process for acquiring 3.0? Thank you. It depends on the type of Software Assurance you own (Enterprise licensees will receive the upgrade license and media automatically), but most SA licensees will have to ask their MBS reseller to order the license keys and media, for a nominal fee (shipping and hand...

Undeliverable #3
I am running SBS 2000 SP4 and I have certain users who's addresses come back as undeliverable unless you fully complete their addresses when typing (not auto-complete). Let's say Our email server (hosted by our ISP) is named 'thatdomain'. Our domain on SBS running Exchange is named 'thisdomain'. When I look at their profilesand email address settings they seem to be identical to the other users who experience no problems. However, when I export the address book into Excel and scroll to the EmailAddress field they show this type of address- '/o=thisdomain/ou...

Capitalising first letter of every line
In my Outlook 2003 emails, the program insists on capitalising the first letter of every line even if not the beginning of a sentence. How can this be stopped? Would using Word as editor be having some effect ? It can be stopped by not pressing Enter at the end of every line you type. Outlook will wrap the lines -- you only need to press Enter at the end of the paragraph. To change the setting so it never caps a line, go to tools, options, spelling, autocorrect button. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solut...

Auto numbering #3
How do I insert an auto number in the invoice# cell in invoice template just like in 2000 version? Hi see: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Regards Frank Kabel Frankfurt, Germany "DOU" <DOU@discussions.microsoft.com> schrieb im Newsbeitrag news:1618CE02-261F-4415-B8D3-CC9F198558A9@microsoft.com... > How do I insert an auto number in the invoice# cell in invoice template just > like in 2000 version? ...

attachment #3
How do you open attachments with another application. If Windows is setup to open .bmp with Internet Explorer and I want to open it in Paint but I do not want to do it all the time then how could I get Outlook to open it and select the application. Basically I am looking for an Open With option. Right click the attachment and "save as", saving it wherever you want. Then go to that location and right click the file and select "open with" "Paul Marques" <dcomputer@hotmail.com> wrote in message news:023a01c3578c$8f13e6f0$a301280a@phx.gbl... > How d...