Hello all,
I'm a very newbish VBAer, and I'm having trouble coming up with a user-defined function that will do what I need. I've written some code (that I will paste below) that will likely highlight my lack of understanding of what I'm doing, but let me explain the setup.
I have a spreadsheet that is going to serve as a log, so that each row corresponds to a unique log entry, and each row is required to have a unique record ID number. Three of the columns of my sheet correspond to the date (mm/dd/yy format), the plant/site (5 possible choices here; let's call them "A, B, C, D, & E"), and the specific building (4 choices here; let's call them "U1, U2, CO, and LI). The format of the unique record ID needs to be like these examples:
12-A-U1-00112-C-CO-01213-B-U2-128
Or more generically, YY-site-building-sequential_number
The hard part about this, I'm discovering, is the sequential number on the end. The first record for a given site and building in a given year should be "001", followed by "002", "003", etc. So it starts over at "001" every year. Also, each unique site-building combination needs to have it's own sequentially advancing number. So in other words, I need to be able to have, for example, "12-A-U1-001" and "12-A-U2-001" both be able to exist simultaneously.
I hope this makes sense. If not, I will be happy to clarify, and I appreciate any help anyone can give me.
The code I'm pasting below (which isn't working) was intended to just handle the number part of my ID key, and then I would string the other pieces onto to it in the spreadsheet. But having it all within the confines of the macro would be much cleaner. Anyway, thanks in advance for any help.
[VBA]
Option Base 1
Public Function assignTAPnum(siteName, unitName, dateYear) As Variant
Dim DateArray() As Variant
Dim PlantArray() As Variant
Dim UnitArray() As Variant
Dim TAPArray() As Integer
Dim i As Integer
Dim j As Integer
Dim LastRow As Integer
Dim NCells As Integer
Dim biggestTAPnum As Integer
'calculates the last used row based on the date of the last entry in the spreadsheet
'which should correspond to the new entry that is currently being entered
LastRow = Range("B2").End(xlDown).Row
NCells = LastRow - 1
ReDim DateArray(NCells)
ReDim PlantArray(NCells)
ReDim UnitArray(NCells)
ReDim TAPArray(NCells)
For i = 2 To LastRow
DateArray(i - 1) = Range("B" & i)
PlantArray(i - 1) = Range("D" & i)
UnitArray(i - 1) = Range("E" & i)
TAPArray(i - 1) = Range("F" & i)
Next i
biggestTAPnum = 0
For j = 1 To NCells
If DateArray(j) = Year(dateYear) And PlantArray(j) = siteName And UnitArray(j) = unitName And TAPArray(j) >= biggestTAPnum Then
biggestTAPnum = TAPArray(j)
End If
Next j
MsgBox biggestTAPnum
assignTAPnum = biggestTAPnum + 1
End Function
[/VBA]
Reply With Quote
Originally Posted by xld
200=""" & c03 & """,E2:E200,0),0),0))") + 1
I've discovered one more wrinkle that has me wracking my brain with no solution so far. I played with the different macros, and after some consideration, I think the original macro that xld gave me actually works best (e.g. I don't mind the ID number incrementing for the instances where the old format was used). But I've discovered that when I sort the columns by date, with newest entry on top, the ID number changes. I need it to be static once it is created so that even if the data gets sorted or a row gets inserted above it or something, it always retains the original number that was assigned.