hi folks, am trying to get a number generator to work for my project register but am having trouble thinking it through from a vba perspective.
I am setting up a Project Register which will record my team's activity this year. Once they have filled in certain fields in a row, I want to generate a project reference number for them. At the moment, I am just trying to get it to work from a Button but will eventually set it to run from a sheet change event which checks to see that certain fields are filled in before generating the number.
The number itself is a combination of text and numerics - "GP10-001" (next will be GP10-002, etc).
I have this so far with only the number component done:
Sub ProjNbr()
Dim r As Integer
Dim rng As Range
Set rng = Range("RefNbrRg") '''this is column I which holds Ref Nbrs
If Worksheets("Project Register").Range("i4") = "" Then '''first proj to be 001
c = Worksheets("Project Register").Range("i4")
r = 1
For Each c In Selection
c.Value = r
r = r + 1
ActiveCell.NumberFormat = "000"
Next c
End If
End Sub
I simply want a sequential number created to the last entry and that new number put in the active row (intersecting with Col I eg I3). I am making a mess of it and would appreciate any suggestions.