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.
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
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.
