98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Item number identifier
Item number identifier
Resolved · Medium Priority · Version 2003
Tue 2 Feb 2010, 10:44Delegate 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:14Trainer 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:49Delegate 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.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Generating Random NumbersTo generate a random number in Excel use the = RAND() function. |
