Would someone please help me with this dilemma? I'm trying to add spaces before non-repeating capital letters in the first row of every sheet in my workbook. I continually deal with workbooks with hundreds of sheets, each with a first row containing column headers that import without spaces.
Example: change "SpacesWouldBeOK" to "Spaces Would Be OK"
I'm trying to understand and learn this stuff, but my mashups of code from various sites have not worked. The closest code I've worked with came from extendoffice.com:
<code class="vb keyword">
This works OK for a single sheet, but it doesn't ignore consecutive capitals (such as "OK"), and requires me to select the range, which is throwing me off with adapting it to the entire workbook with code such as </code></code>
Example: change "SpacesWouldBeOK" to "Spaces Would Be OK"
I'm trying to understand and learn this stuff, but my mashups of code from various sites have not worked. The closest code I've worked with came from extendoffice.com:
<code class="vb keyword">
Code:
Sub</code> <code class="vb plain">AddSpacesRange()</code>
<code class="vb comments">'Update 20140723</code>
<code class="vb keyword">Dim</code> <code class="vb plain">Rng </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb keyword">Dim</code> <code class="vb plain">WorkRng </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xOut </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xValue </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb plain">xTitleId = </code><code class="vb string">"KutoolsforExcel"</code>
<code class="vb keyword">Set</code> <code class="vb plain">WorkRng = Application.Selection</code>
<code class="vb keyword">Set</code> <code class="vb plain">WorkRng = Application.InputBox(</code><code class="vb string">"Range"</code><code class="vb plain">, xTitleId, WorkRng.Address, Type:=8)</code>
<code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">False</code>
<code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">Rng </code><code class="vb keyword">In</code> <code class="vb plain">WorkRng</code>
<code class="vb spaces"> </code><code class="vb plain">xValue = Rng.Value</code>
<code class="vb spaces"> </code><code class="vb plain">xOut = VBA.Left(xValue, 1)</code>
<code class="vb spaces"> </code><code class="vb keyword">For</code> <code class="vb plain">i = 2 </code><code class="vb keyword">To</code> <code class="vb plain">VBA.Len(xValue)</code>
<code class="vb spaces"> </code><code class="vb plain">xAsc = VBA.Asc(VBA.Mid(xValue, i, 1))</code>
<code class="vb spaces"> </code><code class="vb keyword">If</code> <code class="vb plain">xAsc >= 65 </code><code class="vb keyword">And</code> <code class="vb plain">xAsc <= 90 </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb plain">xOut = xOut & </code><code class="vb string">" "</code> <code class="vb plain">& VBA.Mid(xValue, i, 1)</code>
<code class="vb spaces"> </code><code class="vb keyword">Else</code>
<code class="vb spaces"> </code><code class="vb plain">xOut = xOut & VBA.Mid(xValue, i, 1)</code>
<code class="vb spaces"> </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces"> </code><code class="vb keyword">Next</code>
<code class="vb spaces"> </code><code class="vb plain">Rng.Value = xOut</code>
<code class="vb keyword">Next</code>
<code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">True</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub<code class="vb keyword">
This works OK for a single sheet, but it doesn't ignore consecutive capitals (such as "OK"), and requires me to select the range, which is throwing me off with adapting it to the entire workbook with code such as </code></code>
Code:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets