VBA code to concatenate strings with integer

[excelman15]

Hi Guys,

Can someone please help me to correct my VBA code to achieve my requirement please.

I have a range of IP using which I want to create a command and I want to do this via macros.

The command part will be same every time, only part that will change is IP. So, I'm trying to achieve this via macros.

Below code is working but it is only reading one value instead of the column.

Expectation:

1. Column will contain n number of IP. So, I want this code to read whole column.
2. The first and last text will be same every time. That's why I want hard code that in the code. This way I will just use the macros on a column and it will automatically print the output using values of the selected column.

Private Sub Concate()

lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Dim myAry(0 To 2) As String
Dim b As String

For i = 2 To lastrow
b = Cells(i, 1).Value

Next

myAry(0) = "add host name"
myAry(1) = b
myAry(2) = "value set"

Range("A15").Value = Join(myAry)
End Sub

Data:

172.16.1.1

172.16.1.2

172.16.1.3

Expected output:

add host name 172.16.1.1 set value

add host name 172.16.1.2 set value

add host name 172.16.1.3 set value

 

[Peter_SSs]

Please don't put your tables in Code tags - it prevents them displaying as tables. I have fixed them for you this time.
Also, see the forum's specific code tags as shown here. My signature block below has more information

It is a little unclear just what you have and where you want the results, but see if you can make use of this.

Sub JoinThem()
  With Worksheets("Sheet1")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = .Worksheet.Evaluate("""add host name ""&" & .Address & "&"" set value""")
    End With
  End With
End Sub

My sample data in column A and the code above has produced the results in column B

excelman15.xlsm
	A	B
1	IP	Output
2	172.16.1.1	add host name 172.16.1.1 set value
3	172.16.1.2	add host name 172.16.1.2 set value
4	172.16.1.3	add host name 172.16.1.3 set value
Sheet1

 

[excelman15]

Please don't put your tables in Code tags - it prevents them displaying as tables. I have fixed them for you this time.
Also, see the forum's specific code tags as shown here. My signature block below has more information

View attachment 81102

It is a little unclear just what you have and where you want the results, but see if you can make use of this.

Sub JoinThem()
  With Worksheets("Sheet1")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = .Worksheet.Evaluate("""add host name ""&" & .Address & "&"" set value""")
    End With
  End With
End Sub

My sample data in column A and the code above has produced the results in column B

excelman15.xlsm
	A	B
1	IP	Output
2	172.16.1.1	add host name 172.16.1.1 set value
3	172.16.1.2	add host name 172.16.1.2 set value
4	172.16.1.3	add host name 172.16.1.3 set value
Sheet1

Hi peter,

Apologize for the formatting of the text above in my post.

The code you shared with me worked for me. Thank you very much for your help.

 

[Peter_SSs]

Apologize for the formatting of the text above in my post.

No problem. When you are new to the forum it takes a while to get used to things.

The code you shared with me worked for me. Thank you very much for your help.

You're welcome. Thanks for the follow-up.

 

[excelman15]

Hi Peter,

I have query to ask.

I want achieve the output to below command and to achieve I used your code.

Command:

add host name "BL_172.16.1.1" IP-address "172.16.1.1" set value

And to achieve now I tried below,

1. I'm trying to concatenate "BL_" with the excel data but not to print it yet on the excel.---> This I achieved using your code.
2. Now as you can see in the command above I'm calling the created data (created using point 1) in the command. I achieved this using below code (Thanks to your help.). In this I just extended the use of your code as per my requirement.

But now I want to achieve 2 things in this.

1. To achieve this I want to store all the data that was created in point 1 above into a variable and then use that variable in the next part of the code to get the final result.
2. Also, I want "" in the code as well because that is important in the command above. This is something that is not achievable for me however I tried.


Used Code:

Sub JoinThem()

  With Worksheets("Sheet1")
   With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = .Worksheet.Evaluate("""add host name "" & ""BL_"" & " & .Address & " & "" ip-address "" & " & .Address & " & "" set value""")
   End With
 End With

 End Sub

Expected :

Sub JoinThem()
dim var1 as string

With Worksheets("Sheet1")
   With .Range("A1", .Range("A" & Rows.Count).End(xlUp))

var1 = 
    .Offset(, 1).Value = .Worksheet.Evaluate("""add host name "" & ""BL_"" & " & .Address & " & "" ip-address "" & " & .Address & " & "" set value""")

    .Offset(, 1).Value = .Worksheet.Evaluate("""add host name "" & " & var1.Address & " & "" ip-address "" & " & .Address & " & "" set value""")
   End With
 End With
 End Sub

 

[Peter_SSs]

You have lost me. Can you show a small set of original data and final expected results with XL2BB?

 

[excelman15]

You have lost me. Can you show a small set of original data and final expected results with XL2BB?

Hi Peter,

Below is the original data and expected output.

Book1
	A	B
1	Original Data	Expected Output
2	1.1.1.1	add host name "BL_1.1.1.1" ip-address "1.1.1.1" set value
3	2.2.2.2	add host name "BL_2.2.2.2" ip-address "2.2.2.2" set value
4	3.3.3.3	add host name "BL_3.3.3.3" ip-address "3.3.3.3" set value
5	4.4.4.4	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value
Sheet1

 

[rlv01]

You can do that by extending the example provided by @Peter_SSs

Sub JoinThem2()
    Dim DQ As String, S As String

    DQ = Chr(34)
    With Worksheets("Sheet1")
        With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            S = DQ & "add host name 'BL_" & DQ & "&" & .Address & "&" & DQ & "' ip-address '" & DQ & "&" & .Address & "&" & DQ & "' set value" & DQ
            .Offset(, 1).Value = .Worksheet.Evaluate(S)
            .Offset(, 1).Replace What:=Chr(39), Replacement:=Chr(34)
        End With
    End With
End Sub

Book1
	A	B	C	D
1	Original Data	VBA Output	Expected Output	Compare
2	1.1.1.1	add host name "BL_1.1.1.1" ip-address "1.1.1.1" set value	add host name "BL_1.1.1.1" ip-address "1.1.1.1" set value	TRUE
3	2.2.2.2	add host name "BL_2.2.2.2" ip-address "2.2.2.2" set value	add host name "BL_2.2.2.2" ip-address "2.2.2.2" set value	TRUE
4	3.3.3.3	add host name "BL_3.3.3.3" ip-address "3.3.3.3" set value	add host name "BL_3.3.3.3" ip-address "3.3.3.3" set value	TRUE
5	4.4.4.4	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	TRUE
6	4.4.4.4	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	TRUE
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=B2=C2
C6	C6	="add host name " & """" & "BL_" & A6 & """" & " ip-address " & """" & A6 & """" & " set value"

 

[Peter_SSs]

Or extend it this way

Sub JoinThem_v2()
  With Worksheets("Sheet1")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = .Worksheet.Evaluate("""add host name """"BL_"" & " & .Address & " & """""" ip-address """""" & " & .Address & " & """""" set value""")
    End With
  End With
End Sub

BTW, if the sheet with this data will be the active sheet when the code is run, less is needed.

Sub JoinThem_v2a()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate("""add host name """"BL_"" & " & .Address & " & """""" ip-address """""" & " & .Address & " & """""" set value""")
  End With
End Sub

The same would be true for my earlier code as well.

 

[excelman15]

You can do that by extending the example provided by @Peter_SSs

Sub JoinThem2()
    Dim DQ As String, S As String

    DQ = Chr(34)
    With Worksheets("Sheet1")
        With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            S = DQ & "add host name 'BL_" & DQ & "&" & .Address & "&" & DQ & "' ip-address '" & DQ & "&" & .Address & "&" & DQ & "' set value" & DQ
            .Offset(, 1).Value = .Worksheet.Evaluate(S)
            .Offset(, 1).Replace What:=Chr(39), Replacement:=Chr(34)
        End With
    End With
End Sub

Book1
	A	B	C	D
1	Original Data	VBA Output	Expected Output	Compare
2	1.1.1.1	add host name "BL_1.1.1.1" ip-address "1.1.1.1" set value	add host name "BL_1.1.1.1" ip-address "1.1.1.1" set value	TRUE
3	2.2.2.2	add host name "BL_2.2.2.2" ip-address "2.2.2.2" set value	add host name "BL_2.2.2.2" ip-address "2.2.2.2" set value	TRUE
4	3.3.3.3	add host name "BL_3.3.3.3" ip-address "3.3.3.3" set value	add host name "BL_3.3.3.3" ip-address "3.3.3.3" set value	TRUE
5	4.4.4.4	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	TRUE
6	4.4.4.4	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	add host name "BL_4.4.4.4" ip-address "4.4.4.4" set value	TRUE
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=B2=C2
C6	C6	="add host name " & """" & "BL_" & A6 & """" & " ip-address " & """" & A6 & """" & " set value"

Thank you @rlv01, it works for me.