Formula in a string variable

[durgy]

I am concatenating 2 strings str1 and str3 in strTotal in Userforms VBA Excel 2007
When I save userform strTotal = str1 & str3 goes in sheet1!A2 and works fine.

Now there is requirement that I have to add a reference to sheet1!B2 in between the string. i.e when I save userform
strTotal = str1 & (something like =B2 ) & str3 goes in sheet1!A2. Later when user enters a value in B2 (which can vary) this value goes between str1 and str3 in sheet1!A2.
I have to do similar for 100 rows.
I am not sure how to do by VBA. I trying to avoid doing this by formula on the sheet.

Thanks

 

[repairman615]

Hello,

Maybe:

<font face=Courier New>strTotal = str1 & Sheets(1).Range("B2").Value & str3</FONT>

 

[durgy]

This works if cell B2 is populated.
What I am looking for is the strTotal = (str1 & reference to B2 & str3) sit in cell A2 (assuming B2 is blank). When a user enters a value in cell B2, this value goes in between str1 and str3 in cell A2.

 

[repairman615]

Hey Durgy,

Would you post your code?

I think I understand what you would like. I am thinking a change event in the worksheet module that would recognize a change in cell B2 and then split/concatenate/join values stored in cell A2.

These values in cell A2 should be seperated with a characture to signify their respective halves. At least I beleive that would make it easier.

Is the strTotal complete without input from cell B2? In other words, does it still have a purpose without input from B2.

 

[durgy]

Here is the code repairman615:

Dim str1 As String
    Dim str3 As String
    Dim strTotal As String
    
    'Value in Column B and D are populated by VBA code
    'Value in Column C is entered by the user
    'So the value in Column A should be concatenation of values in ColB, ColC and ColD
    'after a value is entered by user in ColC
    
    str1 = Sheet1.Range("B2")
    str3 = Sheet1.Range("D2")
    
    strTotal = str1 & " " & str3
    Sheet1.Range("A2") = strTotal

strTotal is a concatenation of strings depending on the Products selected by the user. Never will str1 or str3 be blank. strTotal includes the strings from VBA code(str1 & str3) and the user input(B2). There can be times when B2 is empty and only str1 and str3 will be concatenated in strTotal(value goes in A2).
Thanks

 

[repairman615]

Hello,

I am confused about str1 being cell B2 and user input. That would equate to the cell being both str1 and str2.

I see that in the comments of the code columns B:B and D:D are populated from VBA and that column C:C is user input.

Below is some VBA that may get closer to your goal. Test it out on a copy to see how it does:

In the worksheet module. Right click the sheet tab and choose Veiw Code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'Application.EnableEvents = False</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Ws.Range("C2:C" & Range("D" & Rows.Count).End(xlUp).Row))<br><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Offset(0, -2).Value = "" <SPAN style="color:#00007F">Or</SPAN> Len(Target.Offset(0, -2).Value) _<br>            - Len(Replace(Target.Offset(0, -2).Value, " ", "")) <> 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>       <br>            Target.Offset(0, -2).Value = Replace(Target.Offset(0, -2).Value, " ", Target.Value)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#007F00">'Application.EnableEvents = True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then in a standard module:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> JoinStrLooped()<br>Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> str1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, str2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, str3 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strTotal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, cRng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Set</SPAN> Ws = Sheets("Sheet1")<br><SPAN style="color:#00007F">Set</SPAN> cRng = Ws.Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> cRng<br>        str1 = Ws.Range("B" & c.Row).Value<br>            <SPAN style="color:#00007F">If</SPAN> Ws.Range("C" & c.Row) <> "" <SPAN style="color:#00007F">Then</SPAN><br>                str2 = Ws.Range("C" & c.Row).Value<br>            <SPAN style="color:#00007F">Else</SPAN><br>                str2 = " "<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        str3 = Ws.Range("D" & c.Row).Value<br>        strTotal = str1 & str2 & str3<br>        Ws.Range("A" & c.Row) = strTotal<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

Forgot to clarify the above uses column C:C for the user input.

 

[durgy]

Sorry, my apologies.
User input is C2 and not B2.
I will check your code.

Thanks

 

[repairman615]

Good to hear,

also the code will work for the all entries for the columns. This is gauged from the last entry in column D:D.

So if your data ends in row 20 in column d, then this will happen to rows 2 through 20.

 

[repairman615]

This works if cell B2 is populated.

Your post did not come through clearly, would you try again?