Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > A repost - Permuting SPACES within a String of words
 
 
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: 46
Default A repost - Permuting SPACES within a String of words
Hi,
I have written an inefficient code (below) of accomplishing what I want
to do. Could anybody help me in shortening it up. Also, presently I
have considered only till 5 words in the original string. For 6 word
combination I will have to write down 32 lines of code more (rather
32*3) and so on. Can this code be made Dynamic.?

Option Explicit

Sub MakeMeMessy()
Dim i As Integer
Dim lastRow As Integer

Range("i2:j65536").Select
Selection.ClearContents

lastRow = Range("AF65536").End(xlUp).Row
Cells(2, "i").Select
For i = 2 To lastRow

Splitwords ActiveSheet.Cells(i, "af").Value

Select Case UBound(arrWords) - LBound(arrWords)

Case 0
ActiveCell.Value = arrWords(LBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 1
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 2
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 3
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 4
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
End Select

Next i


End Sub


Public Sub Splitwords(sText As String)

Dim x As Integer
Dim arrReplace As Variant

arrReplace = Array(vbTab, ":", ";", ".", ",", "-", "/", "\",
Chr(10), Chr(13))
For x = LBound(arrReplace) To UBound(arrReplace)
sText = Replace(sText, arrReplace(x), " ")
Next x

arrWords = Split(Application.WorksheetFunction.Trim(sText), " ")

End Sub

Regards,
Hari
India

Reply With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

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
Find String in another string - only between spaces Nir Excel Worksheet Functions 9 November 2nd 06 11:31 AM
Remove spaces between words Heather Tavitian Excel Discussion (Misc queries) 2 October 1st 05 01:47 AM
Permuting compound words - With or Without SPACES Hari Prasadh Excel Programming 2 January 31st 05 03:02 PM
macro to eliminate spaces between words CSAM Excel Discussion (Misc queries) 3 December 17th 04 11:39 AM
counting spaces in a string xnman Excel Programming 7 May 9th 04 03:06 PM


All times are GMT +1. The time now is 02:25 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"

 

Contact Us ExcelBanter forum home Privacy Statement