Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good code, need one more step
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 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 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, 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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good code, need one more step
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good code, need one more step
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. 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 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 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, 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Good code, need one more step
"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/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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|