cell D5 was formatted with data validation to allow only Cities from column B to appear in drop down list... and it was set to give you a message if you enter a city that is not in column B list...
Code:
Sub concatenate()
Dim nextRow As Long, i As Integer
With Sheets("Sheet1")
For i = 5 To 9
nextRow = Range("G" & Rows.Count).End(xlUp).Row + 1
Cells(nextRow, "G").Value = Cells(2, "A").Value & Cells(i, "D").Value
Cells(nextRow + 1, "G").Value = Cells(3, "A").Value & Cells(i, "D").Value
Cells(nextRow + 2, "G").Value = Cells(4, "A").Value & Cells(i, "D").Value
Cells(nextRow + 3, "G").Value = Cells(5, "A").Value & Cells(i, "D").Value
Next i
MsgBox "Mission accomplished! :)"
End With
End Sub
Here is the new code for what you need. Now it is set to take values from range D5 to D9, but you can set your desired number of cities by changing in this line:
to
... And if you do not want the message box to appear, just delete code line 11