Good code, need one more step

Discussion in 'Microsoft Excel Programming' started by Howard, Aug 11, 2012.

  1. Howard

    Howard Guest

    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,
     
    Howard, Aug 11, 2012
    #1
    1. Advertisements

  2. 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.
     
    Living the Dream, Aug 11, 2012
    #2
    1. Advertisements

  3. Howard

    Don Guillett Guest

    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.
     
    Don Guillett, Aug 11, 2012
    #3
  4. Howard

    joeu2004 Guest

    "Howard" <> 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/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
     
    joeu2004, Aug 11, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Louis

    VBA macro step-by-step

    Louis, Nov 14, 2003, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    5,823
    Dave Peterson
    Nov 15, 2003
  2. hotherps

    need to ad one more step to code

    hotherps, Jul 23, 2004, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    185
    hotherps
    Jul 23, 2004
  3. Rinze Smit

    running macro step by step different from normal run??

    Rinze Smit, Jun 30, 2005, in forum: Microsoft Excel Programming
    Replies:
    10
    Views:
    396
    Rinze Smit
    Jul 26, 2005
  4. al007

    shortcut key to see a macro running "step by step"

    al007, Nov 10, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    242
    PCLIVE
    Nov 10, 2005
  5. PBM

    run macro step by step. excel 2003

    PBM, Nov 19, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    1,923
Loading...

Share This Page