Item number identifier

Resolved · Medium Priority · Version 2003

Tue 2 Feb 2010, 10:44 Delegate Edmund said...

Edmund has attended:
Excel VBA Intro Intermediate course

Item number identifier

Hi, I need to automatically give a list of asset data a unique number but each asset has 7 rows of information all of which must have the same asset number i.e for asset 1 these 7 rows must have 1 in the column next to them and then the next 7 rows must have number 2 next to them for asset number 2 etc etc e.g;
1 Description
1 Asset Category
1 Forecast NBV at Jan 2010
1 Monthly Depreciation
1 Start Date
1 End Date
2 Description
2 Asset Category
2Forecast NBV at Jan 2010
2 Monthly Depreciation
2 Start Date
2 End Date
How van I program VBA to do this? I assume its some kind of loop for 7 rows but how do I then give each block of 7 rows of data a different and sequential asset number? Hope you can help! Thanks.Ed.

Wed 3 Feb 2010, 07:14 Trainer Anthony said...

RE: item number identifier

Hi Edmund, thanks for your query. Try this code out on a blank worksheet first, then adapt to your own table of data:

sub listnumber()

Dim myuniquenumber As Integer
Dim currentrow As Integer
Dim thenumberoftimeiwanttodothis As Integer
Dim RowCount

myuniquenumber = 1
currentrow = 0

For thenumberoftimeiwanttodothis = 1 To 3

For RowCount = 1 To 7

currentrow = currentrow + 1

Cells(currentrow, 1).Value = myuniquenumber

Next RowCount

myuniquenumber = myuniquenumber + 1

Next thenumberoftimeiwanttodothis

End Sub

Hope this helps,

Anthony

Mon 8 Feb 2010, 16:49 Delegate Edmund said...

RE: item number identifier and additional queries

Hi Anthony, thanks for your help however I am still really struggling to get to grips with VB for Excel. I am trying to write a macro to convert sections of data in rows in one sheet into columns in blocks of 8 in a new sheet whilst repeating the same 8 column headers within every block of 8. I have managed to write bits of code to get one block of 8 to work ok but I cannot work out how to do all the steps, transposes and loops and how to streamline everything to get all of the data converted.Can I send you my excel file so you can see what I mean and hopefully fix my code?

On another issue we have files that import data from an external software(Cognos) and then create pivot tables. Is it possible to create a user interface that selects the options for each pivot table to make it easier for the user instead of them having to go into the pivot table and pivot tabel wizard?

Hope you can help!

Kind regards,Ed.

Mon 8 Feb 2010, 16:51 Delegate Edmund said...

RE: item number identifier and additional queries

Sorry - I meant that I needed to convert data from columns in one sheet into rows in a new sheet and not rows to columns as per my previous message!

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips