Solved: Is this a Vlookup problem?

Hi All

Scratching my head for a couple of hours with this.

On Sheet1 - Range B2:B400 are Surnames - Range C2:C400 are Titles
eg. Bloggs J.

On Sheet2 - Range A9:A409 I have J. Bloggs in the same cell

At the moment I am entering the data twice is it possible to just enter the data on Sheet 1 in the format shown above and the data would transfer automatically to Sheet2 in the format required.

Many Thanks

Sooty8

Hi Sooty,

on sheet2 - Range A9 write the formula:

=C2 & " " & B2

drag down this formula till all titles are coverd.

this is the simple way. there is other way of doing this is by macro too.

Hi Jigar

Thanks for the help much appreciated - will have a go recording a macro and try to set it automatically

Regards

Sooty

Hi Jigar

Just thought I'd let you know Recorded the macro placed it on a commandbutton works perfectly.

Many Thanks

Sooty8

Hi sooty,

I written a macro for you:

[VBA]
Option Explicit
Sub CpyData()
Dim i As Long, iLastRow As Long
iLastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To iLastRow
Worksheets("Sheet2").Range("A" & i + 7).Value = Worksheets("Sheet1").Range("C" & i).Value & " " & Worksheets("Sheet1").Range("B" & i).Value
Next i
End Sub
[/VBA]