VBA To enter certain data the concatenate cells in columns

Hi, I have another challenge to overcome:

I need to enter a certain range of data into a spreadsheet , then concatenate the cells, save the concatenated columns as values and then delete the columns that provided the original data. This is for recorded delivery postal tracking label serial numbers.

Each batch of labels contains 100 labels. The serial numbers is composed of the following elements to be entered in the cells as follows:


The format of the number is as follows, as they would be entered into the spread sheet

A1 is 2 letters. These letters are the same for all labels in the batch and for some other batches I have, but it will change for future batches

B1 is 4 digits. These numbers are the same for all labels in the batch and for one batche I have, but I have some newer batches that have a different number

C1 is 4 digits. These numbers are sequential for each label in that particular batch If the batches are sequential to each other, these numbers should also continue sequentially across the batches

D1 is a random 1 digit number. This has no discernable pattern so the number will always need to be entered manually

E1 is 2 letters. these are ALWAYS "GB"


Example Batches
batch 1 has the series KS 3626 1202 3 GB to KS 3626 1301 5 GB

batch 2 has the series KS 7314 0502 7 GB to KS 7314 0601 9 GB

batch 3 has the series KS 7314 0602 2 GB to KS 7314 0701 4 GB

batch 4 has the series KS 7314 0702 8 GB to KS 7314 0801 0 GB

batch 5 has the series KS 7314 3802 5 GB to KS 7314 3901 7 GB

batch 6 has the series KS 7314 3902 5 GB to KS 7314 4001 5 GB

As you can see from the batch numbers, batch 1 is a very early batch; batches 2 to 4 are from recently issued batches and each batch is sequential. Batch 4 and 5 are from even more recently issued batches and each batch is sequential.

So Row 1 will contain
A1 = "KS" B1= "3626" C1="1202" D1="3" E1="GB" F1 is concatenated "KS362612023GB"
Row 600 (if it can be done in one shot) will contain
A600 = "KS" B600= "7314" C600="4001" D600="5" E6000"GB" F600 is concatenated "KS731440015GB"


Ideally, I would like to do as much as possible by entering each of the serial number components once and then get the VBA to complete the cells and the concatenation.

Presumably this would entail a snap box appearing for each of the cols, except D which will be manually entered, and col E which could be defaulted to value "GB"



Using only batch 1 above as the example;

Column A - Batch Length "100", Value "KS"

Column B - Batch Length "100", Values "3626"

Column C - Batch Length "100", Starting Value "1202" (so the last entry will be "1301")

Column D - this will always be a random number that I will have to enter to every cell in the column

Column E - Batch Length "100",This is ALWAYS "GB"


In the above example I've processed 1 batch of 100 labels at a time, but I'm wondering if it could be made to take care of all the batches in one go. For future batches I'm hoping to get say 5 batches (500 labels) that have the same values for all rows in col A and all rows in col B and be fully sequential for col C



After it has done all this, in col F it needs to concatenate the cells in A to E, then save as values which will enable cols A to E to be deleted, just leaving the concatenated column.


Here's a basic "Record Macro" that I did to kick things off, but no doubt it will likely be of no use to you wizzes out there!!

Please Login or Register  to view this content.

I hope some or all of this can be done, but I don't have any workable knowledge of VBA to even know where to start...

Thanks in anticipation / trepidation
Kindest regards
Mike

Hi, thanks for the quick reply.

Your code will concatenate the cells if I have previously entered all data into each of the 100 rows, but I was hoping that I could get a macro to complete the other 99 rows if I entered the first row manually.

I now realise that before I run the macro I will have to type in all of col D manually as this is a random number with no recurring pattern to be able to automate it, but I would like to get the rest done by macro.

So to recap, if row 1 col A to C was typed thus KS 3626 1202,

Row 2 would look like KS 3626 1203,

Row 3 would look like KS 3626 1204, etc etc.

Your VBA puts the "GB" in col E so that would be retained as would the concatenation, save as, and col delete

As previously noted I would have already have manually entered all 100 rows for Col D, so cols A and B are same values for all the rows and Col C is sequential from row A.

Since sleeping on my first post I think it probably unrealistic to be able to do more than 100 rows at a time, not least due to the fact that I have to keep entering Col D manually. However if the macro could also be made to work on every row that has data in col D it would give me the flexibility to do more than 100 rows at a time.

I would appreciate your further thoughts....

Thanks again for your continuing help
Kind regards
mike