LittleGriff
New Member
- Joined
- Jan 13, 2012
- Messages
- 11
Also posted to reddit at r/excel https://www.reddit.com/r/excel/comments/bjp4di/vba_split_strings_into_odd_and_even_arrays_then/. No replies yet.
TL;DR Can I split a text string in such a way that each element can be used in an expression when I loop through the array? Can I format those elements as strings in the same loop or must I use a new one?
I have a column that holds data in the pattern below. I'm providing two cells only for reference, but I need to loop through an entire column of this sort. If users don't fill in the percentage and currency amount, my report returns the top cell with the text "Next Rate" and "Threshold" repeated. If input is provided, I need to deal with the format in the second cell, extracting the decimal figure and the long integer into an array. I have done this by using Replace to turn the first cell into a pattern of semicolons that I condition against to ignore the first cell's contents, and to normalize the layout of the second cell.
My array looks like 0.014; 250000000; 0.013; 500000000; etc.
My problem is that I need to then "write" out the array with formatting, using each array element in a separate part of a single expression. Below is what the output should look like. Please ignore "Highest Rate 1.5%" as I pull that in from another cell and I am able to deal with that. Please also ignore that the screenshot values don't match up. I took the shots at different points in my work.
Here's my code -- my best, failed guess at how to accomplish this. I was hoping the Limit argument would help me specify when each type of element is appropriate. I also hoped that the Like operator might help me send the decimals to one array and the integers to another.
Please forgive my code -- I'm cleaning out my notes as best I can, but I try to document what I'm doing so I don't accidentally blow away something that works.
My respect if you've read this far! Thanks for your help. Reddit says to press tab before pasting your code, but it doesn't seem to be working for me on the desktop. Apologies!
<code></code>
TL;DR Can I split a text string in such a way that each element can be used in an expression when I loop through the array? Can I format those elements as strings in the same loop or must I use a new one?
I have a column that holds data in the pattern below. I'm providing two cells only for reference, but I need to loop through an entire column of this sort. If users don't fill in the percentage and currency amount, my report returns the top cell with the text "Next Rate" and "Threshold" repeated. If input is provided, I need to deal with the format in the second cell, extracting the decimal figure and the long integer into an array. I have done this by using Replace to turn the first cell into a pattern of semicolons that I condition against to ignore the first cell's contents, and to normalize the layout of the second cell.
My array looks like 0.014; 250000000; 0.013; 500000000; etc.
My problem is that I need to then "write" out the array with formatting, using each array element in a separate part of a single expression. Below is what the output should look like. Please ignore "Highest Rate 1.5%" as I pull that in from another cell and I am able to deal with that. Please also ignore that the screenshot values don't match up. I took the shots at different points in my work.
Here's my code -- my best, failed guess at how to accomplish this. I was hoping the Limit argument would help me specify when each type of element is appropriate. I also hoped that the Like operator might help me send the decimals to one array and the integers to another.
Please forgive my code -- I'm cleaning out my notes as best I can, but I try to document what I'm doing so I don't accidentally blow away something that works.
My respect if you've read this far! Thanks for your help. Reddit says to press tab before pasting your code, but it doesn't seem to be working for me on the desktop. Apologies!
<code></code>
Code:
<code>
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Sub Split_Threshold()</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim arr1() As String</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim text_string As Variant</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim myRange As Range</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim newString As String</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim startrow As Long</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim lastrow As Long</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim i As Long 'counter to loop through cells</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim stpr As Long ' counter to loop through elements in the array</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim firstRate As String</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Dim nextRates As String</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Set myRange = Range("BC2", Range("BC1").End(xlDown))</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">myRange.Replace what:=" Next Rate ", replacement:="; "</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">myRange.Replace what:=" Threshold ;", replacement:="; "</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">myRange.Replace what:="0000000", replacement:="mil" 'read up on trim to do this correctly</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">'loop through each cell</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">startrow = 2</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">lastrow = Sheets("owssvr").Cells(Sheets("owssvr").Rows.Count, 1).End(xlUp).Row</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">For i = startrow To lastrow</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">firstRate = Sheets("owssvr").Range("BB" & i).Value</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">nextRates = Sheets("owssvr").Range("BC" & i).Value</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">If nextRates <> "; ; ; ; " Then</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">text_string = Sheets("owssvr").Range("BC" & i).Value</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">'split each cell into an array</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">arr1 = Split(text_string, "; ")</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">ReDim arr1(1 To UBound(arr1))</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">For stpr = LBound(arr1) To UBound(arr1)</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">'For Each text_string In arr1</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">' NOW I'M JUST GUESSING</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">newString = arr1(stpr, 1) & "Next Rate" & arr1(stpr, 2) & " Threshold" & vbNewLine _</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">& arr1(stpr, 3) & "Next Rate" & arr1(stpr, 4) & " Threshold" & vbNewLine _</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">'' & arr1(4) & "Next Rate" & arr1(5) & " Threshold" & vbNewLine _</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">'' & arr1(7) & "Next Rate" & arr1(8) & " Threshold" & vbNewLine _</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Next stpr</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">'Format(Worksheets("owssvr").Range("BB" & i).Value, "0.00%") & _</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">vbNewLine &</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">' Worksheets("owssvr").Range("BB" & i).Value = newString</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">End If</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">Next i</code>[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']<code class="s1w8oh2o-7 bpZJor" style="margin: 0px 2px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Monaco, Consolas, monospace; vertical-align: baseline; background: rgba(238, 238, 238, 0.8); color: rgb(255, 0, 109); max-width: 100%; overflow: auto;">End Sub
</code>[/FONT][/COLOR]</code>