VBA code to concatenate strings with integer

excelman15 · Dec 16, 2022

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.

Code:

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 · Dec 16, 2022

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.

VBA Code:

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 · Dec 16, 2022

Peter_SSs said:
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.

VBA Code:

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 · Dec 17, 2022

excelman15 said:
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.

excelman15 said:
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 · Dec 25, 2022

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:

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 :

Code:

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 · Dec 25, 2022

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

excelman15 · Dec 25, 2022

Peter_SSs said:
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 · Dec 25, 2022

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

VBA Code:

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

TRUE

3

2.2.2.2

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

TRUE

5

4.4.4.4

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

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 · Dec 25, 2022

Or extend it this way

VBA Code:

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.

VBA Code:

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 · Dec 26, 2022

rlv01 said:
You can do that by extending the example provided by @Peter_SSs

VBA Code:

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.

VBA code to concatenate strings with integer

excelman15

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

excelman15

New Member

Peter_SSs

MrExcel MVP, Moderator

excelman15

New Member

Peter_SSs

MrExcel MVP, Moderator

excelman15

New Member

rlv01

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

excelman15

New Member

Similar threads

Forum statistics

Share this page

VBA code to concatenate strings with integer

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Well-known Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock