# 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.


```
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.1172.16.1.2172.16.1.3


Expected output:



add host name 172.16.1.1 set valueadd host name 172.16.1.2 set valueadd 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.


```
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.xlsmAB1IPOutput2172.16.1.1add host name 172.16.1.1 set value3172.16.1.2add host name 172.16.1.2 set value4172.16.1.3add host name 172.16.1.3 set valueSheet1


----------



## 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
> ...


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:


```
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 (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.

Book1AB1Original DataExpected Output21.1.1.1add host name "BL_1.1.1.1" ip-address "1.1.1.1" set value32.2.2.2add host name "BL_2.2.2.2" ip-address "2.2.2.2" set value43.3.3.3add host name "BL_3.3.3.3" ip-address "3.3.3.3" set value54.4.4.4add host name "BL_4.4.4.4" ip-address "4.4.4.4" set valueSheet1


----------



## rlv01 (Dec 25, 2022)

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
```

Book1ABCD1Original DataVBA OutputExpected OutputCompare21.1.1.1add host name "BL_1.1.1.1" ip-address "1.1.1.1" set valueadd host name "BL_1.1.1.1" ip-address "1.1.1.1" set valueTRUE32.2.2.2add host name "BL_2.2.2.2" ip-address "2.2.2.2" set valueadd host name "BL_2.2.2.2" ip-address "2.2.2.2" set valueTRUE43.3.3.3add host name "BL_3.3.3.3" ip-address "3.3.3.3" set valueadd host name "BL_3.3.3.3" ip-address "3.3.3.3" set valueTRUE54.4.4.4add host name "BL_4.4.4.4" ip-address "4.4.4.4" set valueadd host name "BL_4.4.4.4" ip-address "4.4.4.4" set valueTRUE64.4.4.4add host name "BL_4.4.4.4" ip-address "4.4.4.4" set valueadd host name "BL_4.4.4.4" ip-address "4.4.4.4" set valueTRUESheet1Cell FormulasRangeFormulaD2:D6D2=B2=C2C6C6="add host name " & """" & "BL_" & A6 & """" & " ip-address " & """" & A6 & """" & " set value"


----------



## Peter_SSs (Dec 25, 2022)

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

rlv01 said:


> You can do that by extending the example provided by @Peter_SSs
> 
> 
> ```
> ...


Thank you @rlv01, it works for me.


----------



## 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.


```
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.1172.16.1.2172.16.1.3


Expected output:



add host name 172.16.1.1 set valueadd host name 172.16.1.2 set valueadd host name 172.16.1.3 set value


----------



## excelman15 (Dec 26, 2022)

Peter_SSs said:


> Or extend it this way
> 
> ```
> Sub JoinThem_v2()
> ...


Thanks again peter, I have already reached to this by extending the use of your previous code. I wanted to divide this VBA code in 2 different parts using variables and then join them in the 3rd part variable like

Input 1
Input 2 
Result = Input 1 + Input 2

But still thank you very much for your help


----------

