Re: replace characters by numbers
From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 03/09/04
- Next message: lawlera: "RE: Excel VB to send selected range as html email attachment"
- Previous message: Wouter: "Re: Command button to go back to a sheet"
- In reply to: Michael E.: "Re: replace characters by numbers"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 9 Mar 2004 08:30:19 -0500
That is a completely different problem. It may appear the same to you, but
in the context of the approach taken by both JE and myself, we took
advantage of the ascii value of the single character. A more general
approach would put a table on another worksheet such as
col a col b
AA 1
AB 2
BB 3
CA 4
RM 5
Name this Table Data1
then the code can be modified as:
Public Sub LettersToNumbers()
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Selection
With rCell
res = Application.Vlookup(.Value,Range("Data1"),2,0)
if not iserror(res) then
.Value = res
End if
End With
Next rCell
Application.ScreenUpdating = True
End Sub
As long as your table includes entries for anything you will find in the
range you are processing (and its replacement value), then the above
approach will work (single letters, double letters, words, sentences,
numbers - mixes of these).
--
Regards,
Tom Ogilvy
"Michael E." <anonymous@discussions.microsoft.com> wrote in message
news:9177AC7D-0DDB-4706-B10F-56FB1000A18E@microsoft.com...
> Hi Tom,
>
> thank you very much. One question: sometimes the replacement logic is
different, in this case two characeters should be replaced by one numer,
e.g.((A2=2; B1=1; C6=4; D4=3). How do I have to change your macro?
>
> Kind regards
> Michael
>
> ----- Tom Ogilvy wrote: -----
>
> Public Sub LettersToNumbers()
> Dim rCell As Range
> vArr = Evaluate("{1,2;2,1;3,4;4,3}")
> Application.ScreenUpdating = False
> For Each rCell In Selection
> With rCell
> .Value = vArr(Asc(UCase(.Text)) - 64, 2)
> End With
> Next rCell
> Application.ScreenUpdating = True
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Michael E." <anonymous@discussions.microsoft.com> wrote in message
> news:BC45F8F5-DB99-45AB-A88F-41C607291CA8@microsoft.com...
> > I think a simple lookup table would do, because the replacement of
> characters by numbers is mainly for statistic reasons. It would be of
great
> help for me if you could include a lookup table (A=2; B=1; C=4; D=3)
in your
> vba-macro.
> >> Thanks a lot
> > Michael
> >> ----- JE McGimpsey wrote: -----
> >> No - for that you'd need a lookup table or an algorithm. What
> > correlations do you expect to see?
> >>> In article
<8C517D4C-B6FA-4543-90CD-68E6D738E755@microsoft.com>,
> > "Michael E." <anonymous@discussions.microsoft.com> wrote:
> >>> Thanks for the reply:
> >> does the macro also work if the correlation between characters and
> numbers is
> >> different?
> >> e.g.: A=2; B=1; C=4; D=3
> >
- Next message: lawlera: "RE: Excel VB to send selected range as html email attachment"
- Previous message: Wouter: "Re: Command button to go back to a sheet"
- In reply to: Michael E.: "Re: replace characters by numbers"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
