Concatenation & Macro

**Barking_Mad** · 02-22-2011, 06:30 AM

Hi,

I have a workbook that takes concatonated information and via a macro populates a student register with names in the cells adjacent to the concatonated ones..

Two of the concatonated results that the macro uses are as follows:

ADP-NUR-AD9/09AB3
ADP-NUR-AD9/09AB3 - DABS 0001

Students associated with ADP-NUR-AD9/09AB3 are populated correctly in the register. However the same running macro then mistakenly goes on to take all the students from ADP-NUR-AD9/09AB3 - DABS 0001 entry and populates them in the same register.

It seems to be failing to recognise the text ' - DABS0001' after the text AB3 and realise that it's a different group of students.

Curiously when producing a register for just the ADP-NUR-AD9/09AB3 - DABS 0001 students it does not take the ones from ADP-NUR-AD9/09AB3 and add them to the same register.

Without me removing bundles of info and attaching a blank copy, can someone guess what might be happening here?

**watersev** · 02-22-2011, 06:45 AM

hi, Barking_Mad, can you provide sample workbook showing original data and result you'd like to obtain?

**Barking_Mad** · 02-22-2011, 08:11 AM

Hi,

Please see attached.

1. On the 'List of Lessons' tab click in the first row to be printed and then on the big red button.

2. Press cancel so you dont print and click back into the Register worksheet.

3. You will see that it is taking the names of both groups of students when it should only be taking the ones from AB1.

4. Look at the 'Names' worksheet and you'll see you the lists of students and the concatonated column that the info is used to grab the info from.

5. The worksheet called 'Info' stores all the different names for the drop down menu lists.

As i said above, the issue appears to be it fails to realise that

ADP-NUR-AD9/09AB1

and

ADP-NUR-AD9/09AB1 - HECS 2073

are different groups. If I take the space from between

ADP-NUR-AD9/09AB1 - HECS 2073

and turn it into

ADP-NUR-AD9/09AB1-HECS 2073

the problem seems to be solved, but without explaining why, this makes other problems for me!

Hoep this makes sense, any question please feel free to ask! Also, the sheet seems to have developed a 'hang' and cells are slow to be clicked on, i presume this is something to do with the macro? Any suggestions?

**watersev** · 02-22-2011, 09:12 AM

it's a bit of a problem to understand full logic, but try to change your "CreateRegister" code with:

Please Login or Register  to view this content.

That will replace " - " on sheet "Names" columns C and E with "-"

**Barking_Mad** · 02-23-2011, 05:31 AM

Thanks for your help. Im getting the following when pasting this back into my original copy and running the macro:

Run-time effort '1004':

Application-defined or object-defined error

highlighting this bit of code:

Please Login or Register  to view this content.

**watersev** · 02-23-2011, 09:01 AM

let me get home today, I will test that on Excel 2007 though I do not see any possible troubles here

**watersev** · 02-23-2011, 07:52 PM

try this out:

Please Login or Register  to view this content.

On sheet "Info" the data goes with spaces(AB1 - HECS 2073) in Group column so should be changed (AB1-HECS 2073). As far as I understand it's entered by hands.