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]