Increasing numbers in a Log

[xOsnailx]

Hi - I have been trying to create a log to track referrals using Excel User Forms - 95% works fine apart from the part where I try to get it to create a unique ref (+1 on the last one). All I get is the same ref each time I try to create a new record, regardless of how many times I try. The ref is logged in Column F of the Data tab. I have a form which other information is added, then a button to save that into the sheet. I also have a text box (named 'RefNo') on the form to show the new ref, butu each time I get AP1, AP1, AP1..

Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Data")

RefNo.Enabled = True
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

If ws.Range("F" & iRow).Value = "" Then
RefNo.Text = "AP1"
ws.Range("F" & iRow).Value = RefNo
Else
RefNo.Text = "AP" & Val(Mid(ws.Cells(iRow, 1).Value, 1)) + 1
ws.Range("F" & iRow + 1).Value = RefNo
End If

Can anyone see anything obviously wrong? Many thanks!

 

[GWteB]

What's difference is between this code and mine :

Your code generates duplicate numbers. When AP1000 is reached your code generates AP101, AP102 and so on.
BTW, I couldn't reproduce the run-time errors the OP was experiencing.

 

[MKLAQ]

@GWteB how should mod the code ? if there is button cancel for instance from the first time . it will save AP1 and if I run again it will increment to become AP2 but if I press cancel , then should clear AP2 and keep AP1 , if incerment and I press cancel should clear not save . any ideas?

 

[GWteB]

@MKLAQ , would suggest to have an additional macro to revert the previous increase.

Private Sub MKLAQ()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    With ws
        iRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        .Range("F" & iRow).Value = ""
        iRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        RefNo.Text = .Range("F" & iRow).Value
    End With
End Sub

 

[MKLAQ]

wow ! you're super great !
thanks so much professional

 

[GWteB]

You're welcome and thanks for the feedback