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

 

[excelman15]

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.

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