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!

 

[KOKOSEK]

Try like this:

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
RefNo.Enabled = True
iRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
Debug.Print iRow
If ws.Range("F" & iRow).Value = "" Then
    ws.Range("F" & iRow).Value = "AP1"
Else
    Debug.Print Mid(ws.Cells(iRow, 6).Value, 3, 1) + 1
    ws.Range("F" & iRow + 1).Value = "AP" & Mid(ws.Cells(iRow, 6).Value, 3, 3) + 1
End If
RefNo.Text =  Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F").Value

 

[JGordon11]

try changing:

Else
RefNo.Text = "AP" & Val(Mid(ws.Cells(iRow, 1).Value, 1)) + 1

to

Else
RefNo.Text = "AP" & Val(Mid(ws.Cells(iRow, 1).Value, 3)) + 1

 

[KOKOSEK]

If referenced into cells in column A but according to: ws.Range("F" & iRow).Value = RefNo.Value, we would like to deal with col. F (IMHO).

 

[xOsnailx]

Try like this:

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
RefNo.Enabled = True
iRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
Debug.Print iRow
If ws.Range("F" & iRow).Value = "" Then
    ws.Range("F" & iRow).Value = "AP1"
Else
    Debug.Print Mid(ws.Cells(iRow, 6).Value, 3, 1) + 1
    ws.Range("F" & iRow + 1).Value = "AP" & Mid(ws.Cells(iRow, 6).Value, 3, 3) + 1
End If
RefNo.Text =  Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F").Value

Thank you - I tried this but it stops at the 'Else' part. Even with nothing in the log yet. If I manually add a AP1, it then stops on the last line but adds 2 records AP2 and AP3 without me adding anything. Trying again adds AP4 and AP5.

First error gets a Error 13 - Type Mismatch , second one (with the AP1 manually added) gets a "Error 424 Object Required."

 

[xOsnailx]

try changing:

Else
RefNo.Text = "AP" & Val(Mid(ws.Cells(iRow, 1).Value, 1)) + 1

to

Else
RefNo.Text = "AP" & Val(Mid(ws.Cells(iRow, 1).Value, 3)) + 1

Again thank you but this didn't work either. No errors - Just same ref again and again.

 

[GWteB]

How about ...

Sub xOsnailx()

    Dim iRow As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Data")

    RefNo.Enabled = True
    With ws
        iRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        If .Range("F" & iRow).Value = "" Then
            RefNo.Text = "AP1"
            .Range("F" & iRow).Value = RefNo
        Else
            RefNo.Text = "AP" & Val(Mid(.Cells(iRow, "F").Value, 3, Len(.Cells(iRow, "F").Value) - 2)) + 1
            .Range("F" & iRow + 1).Value = RefNo
        End If
    End With
End Sub

 

[xOsnailx]

How about ...

Sub xOsnailx()

    Dim iRow As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Data")

    RefNo.Enabled = True
    With ws
        iRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        If .Range("F" & iRow).Value = "" Then
            RefNo.Text = "AP1"
            .Range("F" & iRow).Value = RefNo
        Else
            RefNo.Text = "AP" & Val(Mid(.Cells(iRow, "F").Value, 3, Len(.Cells(iRow, "F").Value) - 2)) + 1
            .Range("F" & iRow + 1).Value = RefNo
        End If
    End With
End Sub

Spot on.. Perfect thank you!

 

[GWteB]

You're welcome & thanks for the feedback

 

[KOKOSEK]

Hmm What's difference is between this code and mine :

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
RefNo.Enabled = True
iRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
If ws.Range("F" & iRow).Value = "" Then
    ws.Range("F" & iRow).Value = "AP1"
Else
    ws.Range("F" & iRow + 1).Value = "AP" & Mid(ws.Cells(iRow, 6).Value, 3, 3) + 1
End If
RefNo.Text =  Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F").Value

(of course not literally) from post #2?