Windows 7 - Good code, need one more step

Asked By Howard on 10-Aug-12 10:02 PM
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 uppe=
r line
of letters (each letter is in separate cell in a named range, RangerTest, w=
hich is A1:Z1) to the lower line (each letter is in separate cell in a name=
d range RtResult, which is A2:Z2).

FYI, the upper line is a coded substitute for OLIVER WENDELL HOLMES.  The b=
ottom line is a further code that recognizes a designated letter in the upp=
er 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.
=20
If I was to designate an E for the upper then there would be three O's in t=
he 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 si=
ngle cell while retaining the two "internal" blanks and excluding the blank=
s 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 av=
ail.
So I resorted to this clunky attempt but it does not bring the "internal" b=
lanks to AE11.  I get XOXXXXXXXXXOOXXOXXX instead.

Range("AE11").Value =3D 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 is probably pretty simple but it eludes me!

Thanks for any help,
Regards,




Living the Dream replied to Howard on 11-Aug-12 07:30 AM
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.
Don Guillett replied to Howard on 11-Aug-12 09:46 AM
s the info I supplied him.  I need one more step.  The code converts the up=
per line
which is A1:Z1) to the lower line (each letter is in separate cell in a na=
med range RtResult, which is A2:Z2).
bottom line is a further code that recognizes a designated letter in the u=
pper 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 th=
e lower, the rest are X's.
the lower and again the rest would be X's.  The precoded words could be an=
y number
OO etc.  There would be no blanks to contend with in these examples.
single cell while retaining the two "internal" blanks and excluding the bla=
nks at the end, of which there are five (V2 thru Z2).
e a space in the "internal" blanks and ignore the blanks at the end, to no =
avail.
blanks to AE11.  I get XOXXXXXXXXXOOXXOXXX instead.

I do not understand. Send me your latest file and an explanation with EXAMPL=
ES.
joeu2004 replied to Howard on 11-Aug-12 01:23 PM
[....]

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/microsoft.public.excel.programming/msg/7e1020225a236fdb
[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