PC Review


Reply
Thread Tools Rate Thread

Good code, need one more step

 
 
Howard
Guest
Posts: n/a
 
      11th Aug 2012
Using excel 2010.

I got this code From Don Guillett Aug 9, 2012 and it works fine as far as the info I supplied him. I need one more step. The code converts the upper line
of letters (each letter is in separate cell in a named range, RangerTest, which is A1:Z1) to the lower line (each letter is in separate cell in a named range RtResult, which is A2:Z2).

FYI, the upper line is a coded substitute for OLIVER WENDELL HOLMES. The bottom line is a further code that recognizes a designated letter in the upper and produces a designated letter in the lower. In this case if there isa B in the upper it produces an O in the lower, hence the four O's in the lower, the rest are X's.

If I was to designate an E for the upper then there would be three O's in the lower and again the rest would be X's. The precoded words could be any number
of common words, like the following. INTERNATIONAL, KNOWLEDGEABLE, KANGAROOetc. There would be no blanks to contend with in these examples.

My problem arises when I try to take the bottom row and transfer it to a single cell while retaining the two "internal" blanks and excluding the blanks at the end, of which there are five (V2 thru Z2).

I have tried to add an additional "Case Is" in the code to make it produce a space in the "internal" blanks and ignore the blanks at the end, to no avail.
So I resorted to this clunky attempt but it does not bring the "internal" blanks to AE11. I get XOXXXXXXXXXOOXXOXXX instead.

Range("AE11").Value = Range("A2") & Range("B2") & Range("C2") & _
Range("D2") & Range("E2") & Range("F2") & _
Range("G2") & Range("H2") & Range("I2") & _
Range("J2") & Range("K2") & Range("L2") & _
Range("M2") & Range("N2") & Range("O2") & _
Range("P2") & Range("Q2") & Range("S2") & _
Range("T2") & Range("U2") & Range("V2") & _
Range("W2") & Range("X2") & Range("Y2") & Range("Z2")

As usual, it's probably pretty simple but it eludes me!

Thanks for any help,
Regards,
 
Reply With Quote
 
 
 
 
Living the Dream
Guest
Posts: n/a
 
      11th Aug 2012
Hi Howard

You could try it this way.. '«hange to suit ( - | ~ | / | \ | . | _ )

Range("AE11").Value = Range("A2") & ", " & Range("B2") & ", " & _
Range("C2") & ", " & Range("D2") & ", " & Range("E2") & ", " & _
Range("F2") & ", " & Range("G2") & ", " & Range("H2") & ", " & _
Range("I2") & ", " & Range("J2") & ", " & Range("K2") & ", " & _
Range("L2") & ", " & Range("M2") & ", " & Range("N2") & ", " & _
Range("O2") & ", " & Range("P2") & ", " & Range("Q2") & ", " & _
Range("S2") & ", " & Range("T2") & ", " & Range("U2") & ", " & _
Range("V2") & ", " & Range("W2") & ", " & Range("X2") & ", " & _
Range("Y2") & ", " & Range("Z2")


It gives you the following...

x, o, x, o, x, o, x, o, x, o, x, o, x, o, x, o, x, x, o, x, o, x, o, x, o

HTH
Mick.
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      11th Aug 2012
On Friday, August 10, 2012 9:02:33 PM UTC-5, Howard wrote:
> Using excel 2010.
>
>
>
> I got this code From Don Guillett Aug 9, 2012 and it works fine as far as the info I supplied him. I need one more step. The code converts the upper line
>
> of letters (each letter is in separate cell in a named range, RangerTest,which is A1:Z1) to the lower line (each letter is in separate cell in a named range RtResult, which is A2:Z2).
>
>
>
> FYI, the upper line is a coded substitute for OLIVER WENDELL HOLMES. Thebottom line is a further code that recognizes a designated letter in the upper and produces a designated letter in the lower. In this case if there is a B in the upper it produces an O in the lower, hence the four O's in the lower, the rest are X's.
>
>
>
> If I was to designate an E for the upper then there would be three O's inthe lower and again the rest would be X's. The precoded words could be any number
>
> of common words, like the following. INTERNATIONAL, KNOWLEDGEABLE, KANGAROO etc. There would be no blanks to contend with in these examples.
>
>
>
> My problem arises when I try to take the bottom row and transfer it to a single cell while retaining the two "internal" blanks and excluding the blanks at the end, of which there are five (V2 thru Z2).
>
>
>
> I have tried to add an additional "Case Is" in the code to make it produce a space in the "internal" blanks and ignore the blanks at the end, to no avail.
>
> So I resorted to this clunky attempt but it does not bring the "internal"blanks to AE11. I get XOXXXXXXXXXOOXXOXXX instead.
>
>
>
> Range("AE11").Value = Range("A2") & Range("B2") & Range("C2") & _
>
> Range("D2") & Range("E2") & Range("F2") & _
>
> Range("G2") & Range("H2") & Range("I2") & _
>
> Range("J2") & Range("K2") & Range("L2") & _
>
> Range("M2") & Range("N2") & Range("O2") & _
>
> Range("P2") & Range("Q2") & Range("S2") & _
>
> Range("T2") & Range("U2") & Range("V2") & _
>
> Range("W2") & Range("X2") & Range("Y2") & Range("Z2")
>
>
>
> As usual, it's probably pretty simple but it eludes me!
>
>
>
> Thanks for any help,
>
> Regards,


I don't understand. Send me your latest file and an explanation with EXAMPLES.
 
Reply With Quote
 
joeu2004
Guest
Posts: n/a
 
      11th Aug 2012
"Howard" <(E-Mail Removed)> wrote:
> I got this code From Don Guillett Aug 9, 2012

[....]
> I have tried to add an additional "Case Is" in the
> code to make it produce a space in the "internal"
> blanks and ignore the blanks at the end, to no avail.


I do not see "this code" in your posting. And I do not understand most of
your description. But if you are referring to the code posted by Don on Aug
9 at
http://groups.google.com/group/micro...1020225a236fdb
[1], perhaps the following will do what you want.


Sub TheTestSas()
Dim myChar As String
Dim RANGERTEST As Range
Dim v As Variant
Dim i As Long, j As Long, k As Long, n As Long, c As Long
Range("RANGERTEST").Offset(8).ClearContents
myChar = Range("A10")
v = Range("RANGERTEST") ' copy into v(1,1),...,v(n,1)
n = UBound(v, 1)
For i = 1 To n ' delete leading blanks
If Len(Trim(v(i, 1))) > 0 Then Exit For
Next
For j = n To 1 Step -1 ' delete trailing blanks
If Len(Trim(v(j, 1))) > 0 Then Exit For
Next
If i <= j Then ' else all blank
n = j - i + 1
ReDim res(1 to n, 1 to 1) As Variant
c = 0
For k = i To j ' keep interstitial blanks
c = c + 1
If Len(Trim(v(k, 1))) > 0 Then
res(c, 1) = IIf(v(k, 1) = myChar, "O", "X")
End If
Next
Range("RANGERTEST").Offset(8).Resize(1, n) = res
End If
End Sub


-----
[1] Don's original code:

Option Explicit
Option Compare Text
Sub TheTestSas()
Dim J As String
Dim RANGERTEST As Range
Dim C As Range
Application.ScreenUpdating = False
J = Range("A10").Value
Range("RANGERTEST").Offset(8).Value = ""
For Each C In Range("RANGERTEST")
If Len(Application.Trim(C)) > 0 Then
Select Case C
Case Is = J: C.Offset(8) = "O"
Case Is <> J: C.Offset(8) = "X"
End Select
End If
Next
Application.ScreenUpdating = True
End Sub

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need a good step by step instruction on set up project with SQL Server Rico Microsoft VB .NET 0 25th Nov 2008 05:25 PM
VS2005: Good step-by-step -DG- Microsoft ADO .NET 5 5th Feb 2006 02:46 AM
ADSL modem, good; Netgear wireless router , good; NIC good; All cables good....can't connect to Internet or network (Repost) Sid9 Windows XP Networking 5 2nd May 2005 10:29 PM
ADSL modem, good; Netgear wireless router , good; NIC good; All cables good....can't connect to Inte net or network Sid9 Windows XP Networking 0 1st May 2005 05:19 AM
Need Step-by-Step for Reinstalling XP =?Utf-8?B?SmlsbGFpbmU=?= Windows XP Help 2 14th Mar 2005 07:22 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:12 AM.