LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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,
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
And in case I don't see you,good afternoon,good evening,and good n msnyc07 Excel Discussion (Misc queries) 1 June 1st 10 11:24 AM
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good davegb Excel Programming 1 May 6th 05 06:35 PM
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good you [email protected] Excel Programming 0 April 27th 05 07:46 PM
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good you [email protected] Excel Programming 23 April 23rd 05 09:26 PM
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good you [email protected] Excel Programming 0 April 22nd 05 03:30 PM


All times are GMT +1. The time now is 10:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"