VBA: Concat loop with Formatting

[NetFlash]

Hi, i'm looking for a solution to the following.
I have a table like this:
<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">Weight</td> <td class="xl25" style="width:48pt" width="64">Index</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt" align="right" height="17">200</td> <td class="xl27" align="right">0.16</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1000</td> <td class="xl28" align="right">0.19</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2000</td> <td class="xl28" align="right">0.44</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3000</td> <td class="xl28" align="right">0.74</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">4000</td> <td class="xl28" align="right">1.10</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">5000</td> <td class="xl28" align="right">1.15</td> </tr> </tbody></table>
And wish to create an output string to put in a single cell (F3) that looks like this:
1/Code/INS/200/0.16*1000/0.19*2000/0.44*3000/0.74*4000/1.10*5000/1.15

The entry starts with: 1/Code/INS
The values from one row are concated with: /
The values of the multiple rows are concated with: *

As i'm new to VBA i can't quite get a working code.
Any and all help is appreciated

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Jul14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Txt = "1/Code/INS/"
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Txt = Txt & Dn & "/" & Dn.Offset(, 1) & "*"
    [COLOR="Navy"]Next[/COLOR] Dn
[F3] = Left(Txt, Len(Txt) - 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[NetFlash]

Whoow MickG, Works like a Charm...

Hope you don't mind my asking the next questions. As i'm starting with VBA i'd like to understand the code.

<!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable     {mso-style-name:"Table Normal";     mso-tstyle-rowband-size:0;     mso-tstyle-colband-size:0;     mso-style-noshow:yes;     mso-style-parent:"";     mso-padding-alt:0in 5.4pt 0in 5.4pt;     mso-para-margin:0in;     mso-para-margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Times New Roman";     mso-ansi-language:#0400;     mso-fareast-language:#0400;     mso-bidi-language:#0400;} </style> <![endif]-->  Sub MG26Jul14
  Dim Rng As Range   '' Range for the amount of records
  Dim Dn As Range    '' not clear what this is for
  Dim Txt As String   '' Variable for my Txt string
   
  '' I'm guessing i'm setting my start range on A2 and end on .End(xlUp)..??
  Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
   
  '' This sets the initial value for my txt
  Txt = "1/Code/INS/"
      For Each Dn In Rng
          Txt = Txt & Dn & "/" & Dn.Offset(, 1) & "*"
      Next Dn
  ''I understand this part truncates the String to get rid of the last *
  [F3] = Left(Txt, Len(Txt) - 1)
  End Sub

Hope you can help me out

 

[MickG]

Looks like You've got the answers to most of the questions.
The only other is the loop throught the Range Object "Rng", where, at each loop it adds the Txt in column "A" and column "B" deliminated accordingly.
"Dn" is also a range object, that is like "Rng" it has methods & properties.
The default property for "Dn" is .value so "Dn" is the same as "Dn.value". If you open the code module and place a dot (".") behind "Dn" or "Rng" then you should see the properties and methods available.
Mick

 

[NetFlash]

Thanks 4 the explanation.
However i've stumbled on something else. In some cases the index is negative and the output code has to have the minus(-)after the index:
<table border="0" cellpadding="0" cellspacing="0" width="128"><tbody><tr style="height:12.75pt" height="17"><td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">Weight</td> <td class="xl25" style="width:48pt" width="64">Index</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt" align="right" height="17">200</td> <td class="xl27" align="right">0.16</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1000</td> <td class="xl28" align="right">0.19</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2000</td> <td class="xl28" align="right">-0.44</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3000</td> <td class="xl28" align="right">-0.74</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">4000</td> <td class="xl28" align="right">-1.10</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">5000</td> <td class="xl28" align="right">-1.15</td></tr></tbody></table>
Code:
1/Code/INS/200/0.16*1000/0.19*2000/0.44-*3000/0.74-*4000/1.10-*5000/1.15-

I'm guessing i should make a copy of Colum B to Colum C.
Loop through, and where the values are negative, do an ABS() function, then str & "-".

Any help is greatly appreciated.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Jul39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] colB [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Txt = "1/Code/INS/"
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        colB = IIf(Dn.Offset(, 1) < 0, Abs(Dn.Offset(, 1)) & "-", Dn.Offset(, 1))
        Txt = Txt & Dn & "/" & colB & "*"
    [COLOR="Navy"]Next[/COLOR] Dn
[F3] = Left(Txt, Len(Txt) - 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[NetFlash]

Thx for the speedy response Mick,
I think I understand. Instead of making a separate column, you opted to format colB in the code. Understandable, since we're looping anyway.
At first I didn't understand the IIf(), but now i understand it's a compacted IF/Else expression.

I learn more about using VBA in excel. Any books/ courses you can recommend. F.E. how did you learn...?

Once again thx.

 

[MickG]

Have a look here !!:-
http://www.mrexcel.com/forum/showthread.php?t=566383&highlight=learning+vba

 

[NetFlash]

GREAT... !!!

Thx

 

[NetFlash]

Picture the following scenario.
In cell A2 i have the following Text:
The quick brown fox jumps over the lazy dog

When I highlight a portion of this text I need a VBA Function to display the length in cell B2.

For instance.
I select A2: The quick brown fox
Cell B2 displays: 19

What i've got so far is this but have no clue how to use mouse events to capture just the selected portion of the text.
I'm guessing the use of Range("").select

Function GetLength(StrName As String)

    GetLength = Len(StrName)
End Function

Any and all help is appreciated