Solved: Numpty question, but I'm not too proud to ask for help...

[Lester]

Hello
I've created this code below. The aim of it is to create a unique identifier name based on the contents of 3 cells in the same row (by concatenating the contents), namely in columns B, C and E. The VB editor highlights the 'assignment' statement in red (syntax is wrong).

So, for example, when i=2, cell A2 should equal 'B2&C2&E2'

Can someone help me out, please?

Public Sub CreateUniqueIdentifier()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
.Cells(1, "A").Value = "Identifier"
For i = 2 To iLastRow
.Cells(i, "A").Value = (B$i)&(C$i)&(E$i)
Next i
End With
End Sub

[Dave]

HTH. Dave

For i = 2 To iLastRow
.Cells(i, "A").Value = .Cells(i, "B").Value _
& Cells(i, "C").Value & Cells(i, "E").Value
Next i

[Lester]

Dave - cheers for the corrections, man.
I am such a dunce. I had also defined iLastRow as:


iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


...but column A was empty, so iLastRow was zero!

I've now changed it to B, thus:


iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row


Gawd!

Many thanks
Lester

[unmarkedhelicopter]

Another "not great" title for a thread.
Lester, you said you were going to try harder !

[Lester]

Another "not great" title for a thread...
Indeed, unmarkedH - not at all succinct. I apologise. It somehow captured the overwhelming feeling of helplessness, at not having anyone to turn to at work to solve the problem.
Cheers

[Aussiebear]

Numpty... Not a word generally heard these days. Any specific value attached?

[Lester]

Numpty... Not a word generally heard these days. Any specific value attached?
Hi Aussibear
...See definition 2 here! (http://www.urbandictionary.com/define.php?term=numpty) Sums me up w.r.t. VBA at the mo'.

Cheers

[paulked]

:rotflmao: I like that dictionary.

[Norie]

Lester

Why loop?

Public Sub CreateUniqueIdentifier()
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Range("B" & .Rows.Count).End(xlUp).Row

.Cells(1, "A").Value = "Identifier"

With .Range("A2:A" & iLastRow)
.Formula = "=B2&C2&E2"
.Value = .Value
End With

End With

End Sub

[Lester]

@Norie...Thank you v.much - I will give that a try and let you know how I get on. It looks like a more succinct solution.

Cheers
Lester

[Lester]

Many thanks, Norie.
Your code worked a treat. You are very helpful.
I suppose avoiding the loop speeds up the calculation.

Cheers
Lester