PDA

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



Lester
11-02-2007, 07:32 AM
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
11-02-2007, 08:19 AM
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
11-02-2007, 08:28 AM
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
11-03-2007, 03:24 AM
Another "not great" title for a thread.
Lester, you said you were going to try harder !

Lester
11-03-2007, 04:04 AM
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
11-03-2007, 04:42 AM
Numpty... Not a word generally heard these days. Any specific value attached?

Lester
11-03-2007, 06:17 AM
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
11-03-2007, 06:41 AM
:rotflmao: I like that dictionary.

Norie
11-03-2007, 07:17 AM
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
11-03-2007, 07:27 AM
@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
11-03-2007, 05:44 PM
Many thanks, Norie.
Your code worked a treat. You are very helpful.
I suppose avoiding the loop speeds up the calculation.

Cheers
Lester