Hi,

I've posted a couple of questions recenly and got great help back and at the end of last week and I though I had what i wanted. But not the case.

I have a simple user form to collate some simple details. When these details are published to the spread sheet I have two sequences I'd like to have with them. The first is an overal sequence for each new entry (which works fine). The second I want is for a sequence to each Workstream. The work stream itself is in column C for each new row and populaed via the form. This is what I thought would happen, using the workstream value I search for the last occurence, get the value and remove the last five digits from the right and add 1.

But what happens is that it only ouputs the workstream name plus 00001. (Workstream00001, rather than increnting it based on the last occurance). I wonder if someone can give me some pointers as to where I am going wrong. Or hould I give up and go back to UNIX.





Sub Find_Last_Occurrence()
 Dim Rng As Range
 LastRow = Range("A65536").End(xlUp).Row
 
  
 With ActiveSheet.Range("E:E")
 Set Rng = .Find(What:=Range("C1"), After:=.Cells(1), _
    LookIn:=xlValues, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=False)
    
    
    Range("e" & LastRow).Value = CSIPForm1.Workstream.Value & Format(Strings.Right(Rng.Value, 5) + 1, "00000")
      
 If Not Rng Is Nothing Then
    
          
    Range("e" & LastRow).Value = CSIPForm1.Workstream.Value & "00001" 

 End If
 End With
 
 End Sub




Private Sub CreateButton1_Click()

    
    LastRow = Range("A65536").End(xlUp).Row
      
    Range("A" & LastRow + 1).Value = "H3G-CSIP-" & Format(Strings.Right(Range("A" & LastRow).Value, 5) + 1, "00000")
    Range("b" & LastRow + 1).Value = Int(Now())
    Range("c" & LastRow + 1).Value = CSIPForm1.Workstream.Value
    Range("d" & LastRow + 1).Value = CSIPForm1.Indicator.Value
       Find_Last_Occurrence
    Range("f" & LastRow + 1).Value = CSIPForm1.CatText.Value
    Range("g" & LastRow + 1).Value = CSIPForm1.IncType.Value
    Range("h" & LastRow + 1).Value = "ICT"
    Range("i" & LastRow + 1).Value = CSIPForm1.ImpactServ.Value
    Range("j" & LastRow + 1).Value = CSIPForm1.Application.Value
    Range("k" & LastRow + 1).Value = CSIPForm1.Priority.Value
    Range("l" & LastRow + 1).Value = CSIPForm1.Title.Value
    Range("m" & LastRow + 1).Value = CSIPForm1.Definition.Value
    Range("n" & LastRow + 1).Value = CSIPForm1.BusImpact.Value
    Range("o" & LastRow + 1).Value = CSIPForm1.Cause.Value
    Range("p" & LastRow + 1).Value = CSIPForm1.Solution.Value
    
     
   CSIPForm1.Hide
    

'End If

End Sub